Slow Performance with SubReports in Crystal 1

[zeekay]

Hi All,

I have written a report in Crystal Reports. The report has one main section and the two sub-reports joined to the main section. SQL for all the three sections is very efficient and runs very quick if I run it in TOAD. But when I join the sub-reports to the main section, the report runs very slow. The first page takes 3 to 4 seconds to display which is fine. But navigation to every next page takes almost the same time. So, If I run the report for department A and it brings 6 products, I would need around 30 seconds to see these 6 pages.

The parameters I am passing in are Year, Department and Product. The sub-reports are joined using all the three parameters. I am using Product parameter in the “Select data in subreport based on field” section.

Any tips in this regard will be highly appreciated.

Thanks,
Zeekay

 

[lbass]

This is the nature of subreports. The linking occurs locally. You might see a slight improvement if you create the Product parameter in the subreport, add it to the sub selection formula, and then just link the parameter from the main report to the subreport using the dropdown in the lower left corner of the subreport linking screen. Not sure it will make a noticable difference. Please report back with your results.

The other option is to eliminate the subs if possible.

-LB

 

[Madawc]

Each instance of a subreport fetches data from the database. Your main-report detail lines process details that were fetched along with other details. But a subreport in the detail line will do a new fetch.

As lbass says, it is better to avoid subreports if possible, especially at detail-line level. Crosstabs are sometimes useful, but probably not in your case.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[zeekay]

Thanks Ibass. I ahve added a parameter on product in both sub reports and added them with the bottom left parameter. It has improved the performance noticeably. Thanks for your help.