cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle tables linked to ms access table

Former Member
0 Kudos

I have 3 oracle tables linked by key and my report returns 160k rows in about 20-25 seconds.

If i add a MS ACCESS table, and link it by name to one of the oracle tables(inner join) the query is still running

The MS ACCESS table is just 2 columns (lookup name/alias name)

i link the oracle table oracletable.lookupname > ms accesstable.lookup.name, and slect mstable.alias name

It does return data and it is correct, just very very slow.

thanks in advance.

Accepted Solutions (0)

Answers (1)

Answers (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi John,

When Crystal Reports processes a report on two (disparate) datasources the queries are individually sent to the respective datasources and the rest of the processing is done locally by Crystal Reports.

So, in this case, a query is sent out to the Oracle Server and it retrieves around 160k records which is stored in a temp file locally.

Another query is sent to the Access database which sends a few records to Crystal. Again, all the records are stored in a temp file locally.

Then, depending upon the links specified in the database expert, Crystal Reports links these two tables "locally" and applies any record selection or additional filtering.

In a normal situation, where all the links and the record selection is performed by the database server, in this case, the simplest of the jobs is passed on to the database server and CR does all the complex, CPU and memory hogging routines.

That's where latency creeps in!

-Abhilash

Former Member
0 Kudos

That is the best explanation I have heard... well done.

abhilash_kumar
Active Contributor
0 Kudos

Thanks Debi !

Former Member
0 Kudos

Thank you both.

So there is no solution ?anything i can tweek ? the query actually failed(it was running for 10 plus hours), something about ROWID.

Is there another db type(excel, text) i could use ?

The ms access table is just a 2 column lookup, that i need the alias name from.

Thanks John

Former Member
0 Kudos

I have done this and Crystal is not always happy with mixing databases but it can be done. There maybe a problem with linking the two databases. Have you done it before? Maybe you should try a simple test report using just one table from each database as they are used in your report.

If that works, look at your data selection and start picking it apart.

Former Member
0 Kudos

Debi, i have linked Oracle/Ms Acces table together in the past. It was slow, but at least it worked.

This just will not work, i've tried just linking 1 oravle table to ms acess, it works, but very very slow.

when the 2nd and 3rd oracle tables are introduced, it simply runs forever.

i left it running last night when i left, and this morning got this error :

Database Connector Error: 'HY000:[Oracle][ODBC][ORA]ORA-01410:

invalid ROWID

[Database Vendor Code:1410 ]'

after 88009 records. Without the ms access table this query returns 160k records in about 30 seconds.

I have tried making the lookup table an xls, and a .csv. All fail.

i don't know ehere to go with this ?

thanks John

Former Member
0 Kudos

Sorry for not getting back to you sooner... we have been dealing with Hurricane Irene here.

How many records in the access table?

Former Member
0 Kudos

Debi, No worries on the delay.

Access table has 41k records.

What i have discovered is that if i create a report from just the 2 column access table, and then export via ODBC to a .mdb using the driver "Driver do Microsoft Access(.mdb) then link this table, the query runs a lot lot faster.

so this driver combo appears to correct the speed issue.

Now the problem i have is that the Oracle tables alone produced a report of 160k records.

When i link to the ms access table, the report produces about 270k ?

Links order is :-

SRMDEVICEALLOCATION.ARRAYDEVICEKEY --> SRMARRAYDEVICE.ARRAYDEVICEKEY

SRMDEVICEALLOCATION.ARRAYKEY --> SRMARRAY.ARRAYKEY

SRMDEVICEALLOCATION.HOSTORHBAALIAS --> CREXPORT.DE_Full_host_wwn_Field2

SRMARRAYDEVICE.ARRAYKEY --> SRMARRAY.ARRAYKEY

all links options are :-

inner join,

not enforced,

link type =

Thanks John

Former Member
0 Kudos

Your links, all inner join:

SRMDEVICEALLOCATION.ARRAYDEVICEKEY = SRMARRAYDEVICE.ARRAYDEVICEKEY

SRMDEVICEALLOCATION.ARRAYKEY = SRMARRAY.ARRAYKEY

SRMDEVICEALLOCATION.HOSTORHBAALIAS = CREXPORT.DE_Full_host_wwn_Field2

SRMARRAYDEVICE.ARRAYKEY = SRMARRAY.ARRAYKEY

I know you have been doing this awhile, so bear with me if I tell you something you already know... It is the little things we overlook that give us the most problems.

You do understand the one-to-many link restriction, which is you can only have one one-to-many link in a report... Yes? Where a record in one table may have more than one match in the linked table... If any two or more links are one to many. it will cause you to get the extra records.

Also It looks like you have a circular reference from

SRMDEVICEALLOCATION to SRMARRAYDEVICE and SRMARRAYDEVICE to SRMARRAY and SRMARRAY back to SRMDEVICEALLOCATION

So I hope that one of these tables is repeated twice in your report as circular references can also cause problems.

Former Member
0 Kudos

Thanks Debi, i did delete the additional SRMDEVICEALLOCATION.ARRAYKEY = SRMARRAY.ARRAYKEY

link

However i do not seem to be able to get the result i need.

Here's an overview of what is happening, and please don't fall asleep reading it

The original query is :-

SRMDEVICEALLOCATION.ARRAYDEVICEKEY --> SRMARRAYDEVICE.ARRAYDEVICEKEY

SRMDEVICEALLOCATION.ARRAYKEY --> SRMARRAY.ARRAYKEY

It Yields 169236 records

If i add the "CREXPORT" table with "inner join"

SRMDEVICEALLOCATION.HOSTORHBAALIAS --> CREXPORT.DE_Full_host_wwn_Field2

it yields 149798 records

and "left outer join" yields

SRMDEVICEALLOCATION.HOSTORHBAALIAS --> CREXPORT.DE_Full_host_wwn_Field2

it yields 263209 records

the srmdeviceallocation table has a field(hostorhbaalias)

which can either be :-

appmd526(8 chars)

or

c000010000003123(16 chars)

when it contains an element of 16 chars, i want to get the

host name from "CREXPORT.DE_Full_host_wwn_Field1" in the "CREXPORT" table

which has 2 fields CREXPORT.DE_Full_host_wwn_Field1 and CREXPORT.DE_Full_host_wwn_Field2

Field1 | Field2

appwd526 | c000010000003123

so if srmdeviceallocation.hostorhbaalias is c000010000003123

i want to display appwd526

If i change the link

SRMDEVICEALLOCATION.HOSTORHBAALIAS --> CREXPORT.DE_Full_host_wwn_Field1

i get > 600k records.

What is the best method(link, records selection, formula) that will return 169236 records using the CREXPORT

table. I always want to return CREXPORT.DE_Full_host_wwn_Field1

is there is a match with SRMDEVICEALLOCATION.HOSTORHBAALIAS, otherwise "not found in alias table"

formula like :-

if {SRMDEVICEALLOCATION.HOSTORHBAALIAS} = {CREXPORT.DE_Full_host_wwn_Field2}

then {CREXPORT.DE_Full_host_wwn_Field1}

else if {SRMDEVICEALLOCATION.HOSTORHBAALIAS} = {CREXPORT.DE_Full_host_wwn_Field1}

then {CREXPORT.DE_Full_host_wwn_Field1}

else "not found in alias table"

The CREXPORT table is purely a link by name not key.

Former Member
0 Kudos

it looks like you may have a one to many, or none relationship between SRMDEVICEALLOCATION.HOSTORHBAALIAS and CREXPORT.DE_Full_host_wwn_Field2 where SRMDEVICEALLOCATION.HOSTORHBAALIAS is finding more than 1 match in CREXPORT.DE_Full_host_wwn_Field2 , or no match.

look at the data in CREXPORT, and group on CREXPORT.DE_Full_host_wwn_Field2. Are you finding multiple records for this field? If so, that is your problem.