cancel
Showing results for 
Search instead for 
Did you mean: 

Crystal Report / Subreport - Performance Issue

Former Member
0 Kudos

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.

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

The trouble with this message window is now I can not see your comments. Any way, check the subreport links, there may be some things you can do there to speed it up. Are you using a Command in the sub report, where is the sub report, is it in a footer, or in a details type section. (say the main report reads 5 records, depending on where the sub report is, it may have to read it's 2 million plus records 5 different times) also in the report options page, there is a check box for grouping on server, it you have a big & bad server, you may want to select this option... one other option is to switch the Sub and the main, do the heavy hitting 2 millon plus, first, then do the other.....

Former Member
0 Kudos

Somehow my question got posted twice - pls refer to the other post for full thread / responses