I've been recently attempting to imrpove the performance of a number of 'legacy' Crystal Reports. One in question contained approximately 7 subreports with the main report returning 300+ records. I saw this an ideal candidate for using solely a SQL command.
I'd re-written the Crystal using said SQL command and then compared the performance of both existing subreport and new SQL command versions using SQL Server Profiler.
The first image shows the existing subreport version:
The second shows the SQL command version:
I was surprised to see the values returned by the SQL command version which seemed vastly larger than the sum of all the 'subreport' performance values. But, on closer inspection I realised that the subreport was only returning data for the first page in Crystal Reports whereas the SQL version was gleaning all the database info in 1 go.
The below image shows the performance of the subreport version when the user refreshes the report on page one and subsequently moves to page two (page one returns two main report rows whereas page report returns three).
One concern that I've been unable to resolve is that Crystal Report seems to create two SQL batches when using a SQL command thus doubling the performance overheads. The same query when run in SQL Server Management Studio records only 1 SQL batch as expected. I need to look into this as I'm not fully sure why this is occuring.