Skip to Content
Former Member
Aug 26, 2009 at 01:38 PM

Crystal Report / Subreport - Performance Issue


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.

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:


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.


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


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.