We help IT Professionals succeed at work.

SSRS "timerendering" is very slow

Medium Priority
3,199 Views
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?

Thanks
Comment
Watch Question

Author

Commented:
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.

Commented:
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.

Cheers,
Yurich

Author

Commented:
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.
Commented:
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,
Yurich

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.