very slow query, any tricks to speed it up?

[mgason]

Hi,
I have a report that takes a really long time to display, in fact Crystal displays not responding for a while, but if left it finally does display. We are talking minutes.

It does access several tables. I can see why it is slow. Is there anything I can do that might speed it up. Change when data is sorted, etc.

here is the table and data layout.

3 tables, batchRef, Weighments, Faults.

batchRef has the Id numbers for all batches made with a date time field tstamp. This is stamped at the end of a batch.

Weighments has a corresponding Id field with multiple weighments for each ID with a tstamp for each weighment.

Faults has the Id field.

The report has 4 parameters, start time, end time, recipe, revision.
Recipe and Revision are in the batchRef table.

We must use the batchRef table to get all Id's time stamped between start and end time, whose recipe and revision fields match the parameters.
We then must remove any batch Id that appears in the Faults table.
Finally show all the weighments for the remaining batch Id's, with some grouping averaging etc.

My tables are linked from batchRef to weighments on Id field
from batchRef to Fault, left outer join on Id field.

My selection statement is

{batchRef.tstamp} >= {?StartDateParam} and {batchRef.tstamp} <= {?EndDateParam} and  isNull({Faults.Id}) and
{Weighments.Recipe} = {?RecipeParam - Recipe} and {Weighments.Rev} = {?RecipeParam - Rev};

I have some formulas do sums and averages, I am not specifying whileprintingrecords or reading, should I be?

thanks for any ideas
mark

 

[lbass]

Please see thread149-1276307.

-LB

 

[Ray1127]

Check the indexes first. Are the tables indexed. Which fields are the indexes based on. What relationship if any is there between the indexes and the parameters of the report? Those are the first things I would check.