Tips to Optimize SSRS Performance

Sub reports are convenient for reuse purposes, but don’t perform well when there are too many sub report instances during the runtime. To avoid the use of sub reports use tools like LookUp , MultiLookUp , LookUpSet, which will bridge different data sources.

 Also consider using Drillthrough Reports or Nesting of Data Regions if they are applicable to your project.

• The initial report should be retrieved using pre-aggregated data from one or two levels of drill-down. 
•  The innermost drill-down level of the initial report should have conditional, parameterized drillthrough actions.
• The drillthrough report will contain the original detail data set, but should be parameterized to only retrieve a subset of data based on the drilldown path (e.g. Time = 2008 Q2, Product = Bikes).

If images are required, then they need to be embedded

Dataset Sizes and Row Counts should be carefully dealt with.

•  Perform sorting / filtering / pre-aggregation within the database.
• Don‘t retrieve any unnecessary data.
• Use uncorrelated sub queries instead of correlated sub queries. (Uncorrelated sub queries have inner SELECT statement that don’t rely on the outer SELECT statement for information. Uncorrelated sub queries run the inner query once instead of running the query for each row when it is returned by the outer query.)

 Page Header/Footer mustbe formatted correctly; pay special attention to them.

• Referencing total page count or complex expressions requires full pagination to show on the first page  so avoid doing this.

Post-Sort should be aggregate.

•  Ex: Previous, RunningValue, First, Last – More expensive to calculate than other aggregates therefore avoid them.

Interactive Sort have a performance impact, particularly when sorting many group or detail instances.

Avoid Blank Pages.

Use page breaks to improve performance in large reports.

Use data set filters instead of query parameters.

•  Using filters retrieves all data, but only relevant data should be displayed to the user. This may be less efficient on an individual report basis in comparison to filtering at the source, however, by doing so data is retrieved once and stored in a snapshot to serve many different user communities.
• When using query parameters revisit the data source for every new value of the query parameters. Filters will provide snapshots and full parameterization.