Tips to improve performance of MS Reporting service reports (SSRS).

Photo credit: visuloqik

A lot of times I got questions from people how to improve SSRS reports. Of course every report is different, but some tips can be applied to every report. Assume we have a report which takes X seconds to display all data on your screen. The total time to generate a SSRS reports can be split into 2 main parts:

1. Time to executed all queries on the SQL Server. Use the SQL profiler to measure the duration of all executed queries. (Y seconds)
2. Time to generated the report on the reporting server, based on the result set of the executed queries. (Z seconds)

Because we know the total time to execute the report and the total duration of all executed queries, you can calculate the time to generate the report (Z seconds). (X = Y + Z). After you know the values for X, Y and Z, you can start focusing on the biggest part.

• Use the SQL Profiler to see which queries are executed when the report is generated. Sometimes you will see more queries being executed than you expected. Every dataset in the report will be executed. A lot of times new datasets are added during building of reports. Check if all datasets are still being used. For instance, datasets for available parameter values. Remove all datasets which are not used anymore.
• A dataset contains more columns than used in the Tablix\list. A lot of times you will see datasets with next syntax:
  
  SELECT * FROM TableX WHERE Column1 = 'Something'.
  
  This is easy when start building the report. It will retrieve all columns of the table. However all columns are not useful and creates a lot of overhead. Assume you uses 3 columns in the tablix\list. Change the syntax of the dataset to only these 3 columns. This can save bookmark lookups and it will save disk I/O on the SQL Server. So you get a dataset with next syntax:
  
  SELECT Column1, Column5, Column 8 FROM TableX WHERE Column1 = 'Something'
• ORDER BY in the dataset differs from the ORDER BY in the Tablix\list. You need to decide where the data will be sorted. It can be done within SQL Server with an ORDER BY clause in the dataset or in by the Reporting server engine. It is not useful to do it on both sites. If an index is available use the ORDER BY in your dataset.
• Use the SQL Profiler to measure the performance of all datasets (Reads, CPU and Duration). Use the SQL Server Management Studio (SSMS) to analyze the execution plan of every dataset.
• Avoid datasets with result sets with a lot of records like more than 250 records. Greater result sets are more like a data export (dump). A lot of times data is GROUPED in the report without an Drill down option. In that scenario do the group by already in your dataset. This will save a lot of data transfer to the SQL Server and it will save the reporting server engine to group the resultset.
• Rendering of the report can take a while if the resultset is very big. Look very critical if such a big resultset is necessary. If details are used in only 5 % of the situations, create another report to display the details. This will avoid the retrieval of all details in 95 % of the situations.
• Use the database documentation of your applications to understand how to retrieve information from the database. For Exact Globe click here to find the database documentation. For Exact Synergy Enterprise click here to find the database documentattion.

Enjoy building high performing reports.