Crystal report very slow with Oracle 10g linked to Ms Access

[huytonscouser]

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 ?

 

[briangriffin]

I don't know that this will be an improvement, but I'm guessing it will.

Bring the Oracle table into Access as a linked table (external data --> ODBC). Then link the tables in Access and use it as your sole datasource in Crystal.

Make sure the fields you're linking on are indexed.

 

[lbass]

Brian's solution is a good one.

That your command is taking 35 minutes to run when run alone means that it probably is not set up to optimize performance. And you should not be linking a command to other tables in general, since any linking will occur locally--all records will be returned from both tables and THEN the linking will occur. Ordinarily you would build additional tables directly into the command and then use it as your sole datasource.

-LB

 

[Turkbear]

Another method would be to use a connection within Oracle to access the Access database using the info here:

http://docs.oracle.com/cd/B19306_01/server.102/b14232/toc.htm

Then follow LB's advice and tune the query using Oracle's tuning tools.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[huytonscouser]

LB, thanks. So there is no advantage to using the oracle "command" and then linking this to the ms access table as all the data is pulled in 1st for both tables ?

is this true of any "command" even if it's linked to a same style db ? i.e command.oracle10g db > oracle 10gb db just asking.

thanks

 

[kray4660]

The problem normally is when you have two different data sources. I believe it forces CR to load all the data into memory before it does anything (continuation of what lbass was saying), thus kills any advantage of using a command.

I hope this helps

 

[lbass]

You could be linking a command to a table that uses the same datasource, and you would still have the performance problem--the linking would occur locally. If you are going to use a command, it should be your sole datasource.

The only time you should use a separate command is if you are populating a parameter list of values, and then you would not link the LOV command to the main report command, and you would not reference any LOV command fields in the main report.

-LB