SSRS Execution Time Types and Optimization

13 Nov
November 13, 2011

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.

* * * *   2 votes
3 replies
  1. Amit Srivastava says:

    Hi ,
    I have SSRS 2012 report having 12432 pages , each page consist of 20 records..
    Now Excel export took lot of time to render data.
    Is there is any way to reduce Excel export time (Rendering time) od ssrs report ?
    Please suggest.

    Reply
  2. admin says:

    Hi Amit,

    Did you try rendering the report in a different format, such as Html, and was that quick or just as slow?

    Can you also paste in the stats you have for each of the SSRS report processing steps when rendered to EXCEL, so:
    > Data Retrieval:
    > Data Processing:
    > Data Rendering:

    One of the options I would recommend trying is to lower the number of pages down by grouping records under the same page (so if you have 40 record per page, you could cut down your number of pages in half), is this something do-able? or do you have a specific business requirements for that number of pages? it just feels awfully a lot.

    It might also be worth trying to optimise other processing times, so the over-all report time looks faster, even if rendering time was still slow. The reason I say that is because Its difficult to optimise for rendering, as we have little control over what goes on in the rendering engine itself, but here are some tips that might help you get started: SSRS Rendering Optimisation, I didnt find them overly helpful, but it always depends on the situation.

    Reply
  3. Amit Srivastava says:

    Hi ,
    I grouped 50 reocrds per page and obviosly number of pages reduces to 5019, but no significant optimisation in excel render, just a matter of few milliseconds, contrary to that, report execution time increases, which is quite obvious.

    Also HTML took lesser time than EXCEL, and it could be as EXCEL is slowest one as you suggest also.

    Please let me know is there any option to reuce excel rendering time.

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply to Amit Srivastava Cancel reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>