Performance Impact with Single Page Rendering in SSRS

Raja

September 1, 2013

Recently during a discussion with eminent BI geeks at work – we had a discussion how SSRS allows us to display an entire record set on just a single page. Considering the amount of performance overhead involved in displaying this record set on that single page. Just how much would that be? To figure out an answer, I set out to develop a few test case scenarios.

To start with, let me walk you through my fact-finding. I defined three different steps:

• Display all records on a single page of the report (ignore this if you already know it)
• Sample dataset and SSRS report setup
• Measure performance – which provides the answer to “How good of an idea is it to do so?”

SQL Server Reporting Services (SSRS) makes it simple and plain to show all of the records on a single page. The screenshot below displays the process; from under the Report Properties, expandInteractiveSize,and set theHeightproperty to0 in.

Here are the detail activities done to accomplish:

• Created a table on SQL Server database with six fields (one VARCHAR field and five DATETIME fields), loading them with 100,000 records.
• Developed a new SSRS report (default.rdl) and placed a table control with six columns, each corresponding to a field my SQL Server table.
• Duplicated the default.rdl (calling it SinglePage.rdl), but updated the InteractiveSize property
• Deployed and published both the reports to the Report Server.

Once the activities were completed, started measuring performance metrics with the steps mentioned below:

• Executed default.rdl (with all default settings), in the browser, noticing that the report showed up in just a few seconds time.
• Then, executed SinglePage.rdl in the browser, and noticed system started becoming unresponsive.

This confirmed overhead was quite significant. To measure it precisely, I executed query using theExecutionLogview that is available under theReportServerdatabase. This query proved what I had thought earlier. It was a definitely a bad idea, and the metrics proved me wrong.

You can see from the query above, theTimeRendering(time spent in the renderer, measured in milliseconds) was about 24 seconds for the SinglePage report – while it was less than a half a second for the paginated one. Not only the size of the report represented by theByteCountfield was 76 MB for the SinglePage report but it was a mere 0.04 MB alone. The facts and findings was much to my surprise, had I increased my testing sample size by getting 100,000 records to display on a single page.

This makes clear and while using tool properties you must be aware of the consequences. Setting theInteractiveSizeproperty on a report should be used judiciously. The performance impact can be phenomenal.

Important Note:The above numbers can vary based on your system resources and is not to be considered as a benchmark.