SSRS "timerendering" is very slow

Posted on 2009-02-18
Last Modified: 2012-06-27
We have several reports setup in SSRS 2005 that are running very slow.

Through looking at the SSRS logs in the ReportServer database, we've deduced it is primarily the TimeRendering that is, by far, the slowest part of the report generation process.  TimeDataRetrieval and TimeProcessing combined may only be a second or two, whereas TimeRendering can sometimes be ten or twenty times that.

Here's an example:
TimeDataRetrieval: 3021
TimeProcessing: 3834
TimeRendering: 126339
ByteCount: 27331584
RowCount: 2178

Not an abnormally huge number of rows, I don't understand why the report is taking so long to render.  We've tried moving the report server to a different, much more powerful server with little affect on rendering time.

During the report generation, w3wp.exe on the SSRS server is pretty much pegged out the processor.

I haven't examined the .RDL files closely yet, I'm not super familiar with the report designer and "best practices" for designing reports.  Can anyone give me some insight on what might be causing the render portion to take so long?  Anything I should look out for or change in terms of design of the RDL's?

Question by:mortimer452
    LVL 3

    Author Comment

    I should mention as well that changing the render format (HTML, PDF, Excel) only make a marginal difference.  MHTML is maybe 10% faster than PDF or Excel.
    LVL 21

    Expert Comment

    If you have repeating calculations in your field, or iterations, or aggregations, or anything what is not straight forward data pulling - all it can cause for reports to take some time to render.

    How big is your report exactly and how long does it take exactly to run it? Are you using a stored procedure or a query in your report? If you're using a store procedure (what you actually should), how long does it take to run it?

    I have a few suggestions, but first answer the questions above.

    LVL 3

    Author Comment

    Hi Yurich,

    The report isn't enormous by any means, of course the amount of data returned is commensurate with the parameters that are sent.  It's a financial report for a restaurant franchise, and the # of locations as well as date range can affect how large it is.  The example above was for only five locations (out of 90 total), one period (28 days) worth of GL detail.

    The queries in SQL don't take long to run, I don't think that is the bottleneck here, as you can see the TimeDataRetrieval is only 3021ms (3 seconds).  It's only returning 2178 records, not sure how many columns there are but I'm sure it's not a huge amount of data.  It's the 126-second render time that's killing me, no one likes waiting 2+ minutes for a report to generate.

    I'm sure there is alot of aggregations and other math going on in the report.  I'll take a look at the RDL and see if some of the aggregations, etc. can be moved to the SQL side rather than relying on SSRS to perform them.
    LVL 21

    Accepted Solution

    so, is it a stored procedure or just a query inside the report?

    make a copy of this report and start deleting columns - one at a time - where any kind of aggregation or calculation (any but straight data) is happening. You will find this way what exactly is causing your troubles, then you can either review the formula or expression you're using or you can move it to your backend (to SQL).

    Good luck,

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now