Oracle db linked to Ms access(.mdb)

[Former Member]

I have a report that links a "oracle command" from an oracle 10g db to a ms access(.mdb) table.

The report runs very very slowly. still running after 2.5 hours....:-)

The command when run by itself returns about 290k records and completes in about 35 mins, and the ms access table has 75k records and complets in about 5 mins.

I am using the ms table as a lookup/insert, when a field in the oracle table returns an unknown value.

The oracle command pulls in about 50 fields, and the ms table is 3 fields.

i am guessing all the data from both sources is being pulled in ?, and then the link is being done in memory or temp disk space, so hence the lonnnnnnng delay ?

any suggestions how i could speed this up ?

[abhilash_kumar]

Hi John,

Yes, CR retrieves the data from the Oracle database first and stores it in a temp table. Then another temp table is created for the Access database. CR then links these temp tables up.

Any record filtering is also done by CR, hence the latency!

Well, it also depends on how fast the machine is (in this scenario)- although I'm not sure how much of an improvement it might bring in.

-Abhilash

[Former Member]

Thanks Abhilash,

The command pulls in about 50 fields, and for this report i am only interested in about 10.

If i modified command to reurn only the fields i needed, would this speed things up ? I know this sounds obvious but, the command is used in several other reports.

What do you think ?

[abhilash_kumar]

Yes, of course, lesser the number of records brought into Crystal, better the performance.

-Abhilash

[Former Member]

Abhilash,

I just noticed the number of records returned in the MS ACCESS table contains many duplicates.

The "show sql query" is :-

SELECT DISTINCT "STS_ACTIVE_ZONESET"."ZONESET_NAME", "STS_ACTIVE_ZONESET"."ZONE_NAME", "STS_ACTIVE_ZONESET"."ZONE_MEMBER"

FROM "STSVIEW"."STS_ACTIVE_ZONESET" "STS_ACTIVE_ZONESET"

ORDER BY "STS_ACTIVE_ZONESET"."ZONESET_NAME"

Yet a subset of the data returned is :-

Host name WWN Fabric

EMKHPIU009 10000000c942de9a A

EMKHPIU009 10000000c942de9a A

So how do i get rid of the duplicates ???? there are many like this. the field that begins with 1000000 is defined as text in ms access.

[abhilash_kumar]

Hi John,

Is this the same query that is returning 'HostName', 'WWN' and 'Fabric'?

-Abhilash

[Former Member]

Yes but Hostname , WWN and Fabric are all formulas.

i just had a light bulb moment !!

Abhilash, i actually build/create/export the ms access table.

I create the data from an oracle db, gathering the 3 fields, and then

export to a .txt file, which i link to a ms access table.

Whicj i then link in another report(this slow one) to another oracle db

So i just had a thought. Am i am to link the 2 oracle dbs, and link them

via name not key.

i.e.

the field i am linking to would be the field name that returns 10000000.....

EMKHPIU009 10000000c942de9a A

and link this filed to the oracle table table, and not use the ms access db at all

?

[abhilash_kumar]

Whoa, that's some project!

Linking the two Oracle dbs should improve the performance I guess.

Give it a shot and let me know.

-Abhilash