Query performance greatly decreases with linked tables?

[RobPouwelse]

Hello again,

I have noticed that when I use linked tables in a report using CR XI, that the performance greatly decreases. A report with 2 tables (3234 and 967 records respectively) will take about 190.000ms to compile (thats about 3 minutes). If I add another table to it, the compile time will increase to about 600.000 ms (10 minutes). Is this due to CR XI or due to a lousy ODBC driver for the custom database?

Thanks in advance,
Rob Pouwelse

 

[Turkbear]

Hi,
If your linking is not optimally set, adding tables can seriously affect run times..Be sure to link from the least # of records table to the others..The leftmost table usually will be eveluated first, then all those returned records will be used to link to the others..so fewer records in the 'driving' table will mean fewer 'trips' to the linked ones..Link by an Indexed field whenever possibe.

What database, version? Can you use a Native connection?





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

 

[RobPouwelse]

I am using an OMNIS database from Raining Data. CR XI does not support a native connection so I have to use an outdated ODBC driver (29-8-2001) because I haven't been able to find a more recent driver.
At the moment I'm working on a program that will extract the data from the OMNIS database and puts it in a dbase format which is supported in CR XI. I'm basicly making a view of the data I need for that particular report. So if this idea works out, I might have a workaround to that problem.

About the linking part, I am linking from a master table to a detail table (something like a customer who made several orders). Wouldn't it be only logical that when I specify a select range (cust.nr X only or cust.nr Y through Z) that it would only have to process the master table once and the detail table an [amount of master records found] amount of times. Because I didn't notice any difference in performance if I used a select range or not. As if CR XI always joins the two tables (which would mean that it has to process the detail table a [master] amount of times)

 

[Turkbear]

Hi,
Unfortunately, the query restrictions ( the WHERE clause) may not be being sent to the database - if that is the case, the report will return ALL the rows in BOTH tables and sort it out in Crystal..

Check the View Sql option under the Database tab to see what is actually being sent as the query...






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

 

[synapsevampire]

Check my FAQ on determining whether the SQL is being passed to the database:

faq767-3825

I assume that you're new to databases based on your description of how a database handles query requests (although each has differences).

I still don't underatnd what you mean by compile times, what are you compiling? Do you mean the query execution time?

If you find that you can't get the SQL to pass to the database, I would suggest posting exactly what you tried here, rather than stating times or descriptions of what you tried. If you decide to consider another extraction layer, I would consider LINKING (not importing) the tables into MS Access and building an MS Access query to base the report on, that's VERY common for disparate databases and might prove useful for you.

Good luck,

-k