SQL Server 2008 Reporting Services slow reports

Question

I have a problem in SQL Server 2008 Reporting Services. The problem is that the report is sometimes too slow to render (it takes more than 30 min), although I took the query and executed it in SQL Server Management Studio and it didn't take more than 25 seconds.

The query returns a large table (about 5000 rows) and I use it to draw a pie chart in the report, I tried to optimize the query so that it returns only 4 rows but the report was slow again.

What confuses me is that sometimes the report (with different input) is as fast as the query (about 30 sec), I thought it might be because of low number of users so I tried with some colleagues to view it at the same time but the reports still are fast, I tried to change in the configuration but I had no luck.

I've been searching for a solution for this problem for more than two months, so if anyone could help me on this I will be very thankful.

Answer 1

If you have access to the ReportServer sql database execute the following query or similar against the ExecutionLog view:

select TimeStart, TimeEnd, TimeDataRetrieval, TimeProcessing, TimeRendering, Status, ReportID from executionlog

This will provide you with a good breakdown of your report rendering (with different parameters). Pay close attention to TimeRendering, TimeProcessing and TimeDataRetrieval. Large or high values for any of these columns will illustrate where your bottleneck is.

One problem that I have experienced in the past is when you are returning a fairly large dataset to the report (5000 rows is large enough for this scenario) and then you are using the inbuilt ssrs filtering, the rendering is very slow and this would result in a very high TimeRendering value. All rendering should be done at the database layer, grouping and filtering does not perform well will large amounts of data when performed in the ssrs report itself.