SQL Server 2008 Reporting Services slow reports

reporting-servicessql-server-2008ssrs-2008

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.

Best Answer

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.

Related Solutions

Sql-server – How to check if a column exists in a SQL Server table

SQL Server 2005 onwards:

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'columnName'
          AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
    -- Column Exists
END

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END

How to connect to local instance of SQL Server 2008 Express

Please check the ServerName which you provided. It should match with the below shown Name in the UserName textbox, and that name should followed with \SQLEXPRESS:

Connect dialog

Best Answer

Related Solutions

Sql-server – How to check if a column exists in a SQL Server table

How to connect to local instance of SQL Server 2008 Express

Related Topic