Link to home
Start Free TrialLog in
Avatar of mortimer452
mortimer452

asked on

SSRS "timerendering" is very slow

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
Avatar of mortimer452
mortimer452

ASKER

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.
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
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.
ASKER CERTIFIED SOLUTION
Avatar of Yurich
Yurich
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial