RS Report Performance Relief in CU7

  • Article

Cumulative Update 7 for SQL 2008 R2 was just released.  Within that CU, there are two fixes for Reporting Services that people should be aware of that related to report performance.

Large HTML

This issue is related to reports that have a large amount of HTML elements.  This could be due to reports that are not paginated, or they have very large page size. Another example would be reports using drill down functionality (toggle or expand/collapse functionality) where expanding out the items in the report results in a large amount of items being displayed.

The actual issue that this fix corrects deals with the “fix up” that is done to the HTML before it is displayed. The “fix up” effectively loops through all the HTML elements in the report. 

FIX: Performance decreases after you move a large report to SQL Server 2008 R2 Reporting Services
https://support.microsoft.com/kb/2506799

Muti-Select Parameters

This issue is related to having a large number of values within a multi-select parameter list.  Large would be something over a few hundred (>300).  You could validate if you are hitting this by limiting the amount of items coming back in the query for the Parameter List to one hundred or less.  If the issue clears up, you are probably hitting this issue.

FIX: Performance decreases after you move a report that contains a large multi-select drop-down parameter list to SQL Server 2008 R2 Reporting Services
https://support.microsoft.com/kb/2522708

The Toolbar

In both cases, the code that actually triggers this behavior is resident within the toolbar of the Report Viewer Control.  You could try hiding the toolbar to see if that works around the issue to try and determine if you are hitting either one of these issues.

How to tell if you are hitting this issue?

One indicator that you may be hitting this issue is to compare the actual time it takes for the report to display vs. what the time looks like within the ExecutionLog3 view within the RS Catalog database.

select ItemPath, TimeStart, TimeEnd, TimeDataRetrieval, TimeProcessing, TimeRendering, Status
from ExecutionLog3

SNAGHTML630890d

Based on these numbers, it looks like the report was really quick.  Although at 11:54, when I’m writing this, it still hasn’t displayed in the browser.  All I see is the spinny.

image

Based on some testing that we did on the CSS side with some repro’s, we found a dramatic decrease in time to render.  In one case, it took 5 minutes to show in the browser, and after CU7 it took about 30 seconds.  Although results will vary depending on your report and whether you were hitting this issue to begin with.

 

Thanks to Matt Hofacker on the RS CSS team for putting a lot of the data for this together!

 

Adam W. Saxton | Microsoft SQL Server Escalation Services
https://twitter.com/awsaxton