Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SSRS "timerendering" is very slow

Posted on 2009-02-18
4
Medium Priority
?
2,769 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
0
Comment
Question by:mortimer452
  • 2
  • 2
4 Comments
 
LVL 3

Author Comment

by:mortimer452
ID: 23683534
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.
0
 
LVL 21

Expert Comment

by:Yurich
ID: 23685451
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
0
 
LVL 3

Author Comment

by:mortimer452
ID: 23685869
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.
0
 
LVL 21

Accepted Solution

by:
Yurich earned 2000 total points
ID: 23686126
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
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Suggested Courses

580 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