Crystal Report / Subreport - Performance Issue

24 Views

I am having problems with a Crystal Report / Subreport with performance. I am using Crystal Version 11, directly in Crystal Developer. There are no other programs involved. I am linking to a SQL Server database using ODBC.

I have narrowed the problem down to this:

The main report has 4 tables. See diagram using the following link. http://screencast.com/t/TA9YYlwwl7

The subreport has 1 table, this table has > 2 Million records in it.

The main report links to the subreport with one field:

Main report field SAMPLE.PATIENT = subreport field Z_RESULT_HISTORY.PHIN

When I set the subreport linking within Crystal it automatically generates the following in the record selection for the subreport:

{Z_RESULT_HISTORY.PHIN} = {?Pm-SAMPLE.PATIENT}

The problem is that the report execution time is dependent on the field that I am using for the record selection in the main report.

Case I works lightening fast:

There are 16 records returned on the Main Report each one of these has about 1-5 records returned on the subreport.

{SAMPLE.PATIENT} = "MOUSEMICKEY" and {SAMPLE.SAMPLE_NUMBER} < 200

Case II brutally slow there are 51 records in the main report that qualify with a few records each in the subreport. By brutally slow I mean a few minutes:

{BATCH.NAME} = "HEP_ARCH-20090420-1"

In this case, I can see in the bottom right of the Crystal Preview window, that it is reading through all 2M records in the Z_RESULT_HISTORY table

Case III brutally slow - a couple minutes

{BATCH_OBJECTS.OBJECT_ID} = 111

This returns 1 record on the main report and 0 records on the subreport. Yet I can see it reading through ALL 2 Million records before the report is displayed.

What I can t understand is why the field used for record selection on the MAIN report is affecting the speed of the execution of the subreport. I need the main report to be selected by BATCH.NAME yet I can t figure out what I can change to make the report run fast. When I record select the main report by SAMPLE.PATIENT, I do NOT see the subreport reading all 2M reocrds - the report preview is returned in less than 1 second.

Any help would be much appreciated.