Performance degraded with three subreports in a report.

[makjoshi]

Hi All,
I'm facing one problem while displaying a report. It is taking too much time to show all the records.
There are two databases from where the records are being fetched. There is Subreport Link between the two fields of Main report and the first Subreport and same for the other two subreports.
I'm getting 200 records from first database and 1,50,000 records from the other (No. of records are from SQLProfiler), but what might be happening is that CR, is doing a linear search means for the first record from the first database it is scanning all the records from the other and after matching the data it is going for the second record and so on.
That might be the reason that it is consuming 80%CPU and taking good amount of time.
Is there any other technique to change this algorithm or any workaround to enhance performance.
Your suggestions are welcome. Thanks in advance.
-Mak

 

[lupins46]

Any subreports will slow the process down. The following may be things you are already doing but anyway...

If there is a conventional parent-child structure then you could do a grouped report based on a query that joins the two tables together and eliminate at least one of the subreports.

If you don't need all the records from the larger table then build a query in the database to select just the ones you want and use that as one of the sources.

 

[dgillz]

Where have you placed the subreport object(s)? If placed in the details section, then the subreport will execute once for every record found in the main report. If it is laced in the group header/footer, it will execute once for every group in the main report.

As you might imagine from this scenario placement of subreports can make a huge difference.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.srhconsulting.com