CR9 Add Command SQL and multiple databases is slow

[tmkraynak]

I need to build a report from multiple databases (Oracle, w 50000 records and Excel w 10000 records). I am using SQL in order to summarize the records and control for many-to-many relationships. I have successfully built the SQL Add Command for each database, individually. When they are individually used in a report, they run literally instantaneously. Good so far.

However when I bring BOTH SQL commands into one report, and (successfully) link them (two fields are linked) the database access and record retrieval slows to a crawl. It now takes more than 6 hours to go to the end of the report. I would like to build ONE SQL statement that hits BOTH databases, but I think that is not technically possible (please tell me I'm wrong).

Is this the correct method for linking multiple COMMAND-driven queries. Or am I asking too much ??

Any suggestions would be appreciated.

 

[synapsevampire]

Crystal is a horrible database engine, much as Excel is a horrible database. Greast for forecasting and what if stuff, but not usable as a database.

I would suggest using MS Access and LINKing the tables into the Access database (not importing), then building a MS Access Query which links the disparate tables for use by the report, it will prove much faster.

Going forward I would persuade the people using Excel as a database to switch to a real database, either Oracle, or if they must use a toy, select MS Access over Excel.

-k

 

[IdoMillet]

You may be able to link the excel data into Oracle, removing the need to use Access.

An alternative would be to use a linked subreport.

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com