Analyzing the performance of your SSRS reports in terms of execution and rendering time is crucial when trying to optimize your SSRS reports. Although SQL Server’s Reporting Service could be a bit of a black box sometimes, usually there are some back-end ways to accomplish most tasks.
This post will go through the SQL Server Reporting Service (SSRS) Execution Types (stages), as well as some top line recommendations on how to optimize the time taken to execute each stage.
To get your SSRS reports execution statistics, you can check out my SSRS Report Execution Statistics and Analysis post.
There are 3 stages that an SSRS report goes through when being executed, these are:
SSRS Report Data Retrieval
This stage is handled by the SQL Server engine (or whichever Data Source used when building the report), and involves retrieving the data from the source as per the defined access interface, so for SQL Server data sources, this will be in the form of Stored Procedure calls or textual queries defined in the SSRS report.
The execution time reported for the data retrieval step is purely the time it took for the SQL Server engine to retrieve the data by running the queries.
How to Optimize Data Retrieval Step
To optimize your data retrieval time, you will need to optimize your queries; this will essentially involve reconsidering the indexing strategy, optimizing the SELECTs and generally making sure your report data source database is agile enough to run the reporting queries quickly.
SSRS Report Processing
Report processing occurs after the data has been retrieved from the data source, and essentially involves preparing the data for rendering by performing all the function calls and aggregations defined in the report. The outcome of this step is the final data ready to be rendered in the desired format.
How to Optimize Report Processing
Since the Report Processing step involves the defined functions and aggregations in your report, this will be the place to look for some performance boost.
Generally it is recommended to do all aggregations and filtering in the SQL Server engine (so in the Data Retrieval stage) rather than leaving the work for the Report Server; this is because the report server is not optimized to handle these types of data aggregations and manipulation (in terms of indexes, data-storage or even server resources), and so a simple aggregation in your report could definitely cause longer executing reports, even if the report aggregations are not actually doing anything (so the data is pre-aggregated in the engine), its very easy to leave an aggregation in an SSRS report that has no functional requirement.
SSRS Report Rendering
The Report Rendering stage varies depending on the selected rendering format, so if you are running the report in HTML format, the rendering time will differ than say PDF format. The SSRS Report Rendering Time is essentially the time it takes to generate the report from the final data into the requested output format.
How to Optimize Report Rendering
Since the Report Rendering Time varies with every rendering format, it is important to choose your final report delivery strategy careful to take advantage of fast rendering types.
Generally HTML is a quick rendering format, although try to avoid delivering very large reports in HTML without proper paging, as this might crash the user’s browser.
EXCEL (XLS) is generally the slowest rendering format.
From a user experience perspective, it is sometimes recommended to initially display the report in a quick rendering format (such as HTML), and give the user the option to export this HTML report to slower rendering types (such as EXCEL), SSRS will not re-run the whole report again (the full 3 steps), and instead will only perform the Report Rendering (last) step. Although over-all this strategy might lead to longer paths for the user-journey to reach their goal, it gives the user quicker feedback and makes them feel the reports are actually more responsive than they might be.