Client-side joins from different data sources - performance

[kvr99]

Using Crystal Reports 9, I am attempting to integrate data which originates in both SQL*Server tables and Oracle tables into a single report. I am able to perform a join on the client-side (by creating two SQL commands and linking them), but performance is impossibly slow, even when I reduce the size of the incoming datasets to a few thousand records.

I believe the problem lies in the fact that I am linking two queries (which have no indexing on the client-side), as opposed to linking two tables. This is necessary in my case since I am performing summary operations on each respective server.

Even so, if I reduce my Oracle dataset to a mere 10 records, and reduce the SQL*Server dataset to a few hundred, it still appears to take 20 minutes to perform the join.

Any thoughts on why this would be? Any suggestions for other approaches? Thanks!!!






I am using Crystal Reports 9.

 

[synapsevampire]

I often resolve this by linking both the Oracle and SQL Server tables into MS Access, and then create a Query in MS Access, which is used to supply data for a report.

-k

 

[kvr99]

Thanks for the tip K. It turns out that when I prepared my respective queries in Crystal SQL Designer and saved the data there, then used these independent .qry files as my data sources (as opposed to creating command objects in Crystal Reports), performance went from 6 hours to 6 minutes!

Apparently, using the latter approach, Crystal tries to read the entire database tables in from each source (without applying the respective filters contained in the individual queries) then tries to link the datasets, or some such thing. Since each table has upwards of 1,000,000 records, this takes hours.

But these leaves me with a different problem. I would like to parameterize my join query by date. I really can't see any way of doing this from Crystal, since I am using saved data sets (.qry files) in the first place. Can you see a way around this? If only I could force crystal to refresh the individual .qry files before attempting to join their data. Sigh......