Tuesday, 16 August 2011

Performance of SQL commands and subreports in Crysal Reports - CR

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).

As this report contains over 70 pages it is plain to see that using the subreport version the database would be queried multiple times and incur all the related network traffic. It is thus beneficial to go with the SQL command version (as originally expected!). Summing up all the CPU and read and writes also confirms this.

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.