Report Performance – Dataset filters vs query parameters?

http://www.sqlchick.com/entries/2012/1/8/relating-parameters-filters-in-sql-server-reporting-services.html

The two types of filters in SSRS are:

  • Dataset Filter.  This is the most common:  filtering within the source query.  When you implement a filter within the dataset, less data is sent over the network from the source database server to the Report Server – usually a good thing.
  • Report Filter.  This includes filtering after the source query has come back – on a data region (like the Tablix), or a data grouping.  When you implement a filter within the report, when the report is re-executed again with different parameter choices, the Report Server uses cached data rather than returning to the database server.  This is a viable option if you intentionally want to return all possible data from the database server in the initial query.  Keep in mind this could be far less performant if the volume of data is high & the report won’t be re-executed numerous times.

Generally speaking, using a Dataset Filter is the most efficient method.

http://technet.microsoft.com/en-us/library/bb395166(v=sql.90).aspx  …

Query Design

A report is processed in the following order: query execution, report processing, and report rendering. To reduce overall processing time, some of the first things you need to decide are which data to retrieve from the data source, and which calculations to include in the report. Reporting Services supports a wide range of data sources, including plain file systems, advanced database servers, and powerful data warehousing systems. The functionality of data sources and the structure of the report determine which operations should be done in the query, as opposed to which operations should be done inside of the report. Although the Reporting Services processing engine is capable of doing complex calculations such as grouping, sorting, filtering, data aggregation, and expression evaluations, it is usually the database system that is best optimized to process some or all of these data operations. With that said, always keep the following in mind:

  • Optimize report queries.Query execution is the first step of the reporting process. Having a good understanding of the performance characteristics of your database system is the first step to good query design. For example, you can use SQL Profiler to track the performance of your queries if you use SQL Server as the database server.
  • Retrieve the minimum amount of data needed in your report.Add conditions to your query to eliminate unnecessary data in the dataset results.If the initial view of the report shows only aggregated data, consider using drill-through to display details. Drill-through reports are not executed until the user clicks the drill-through link, whereas show/hide reports or subreports are processed during the initial report processing, even if they are hidden.
Advertisements

Leave a comment

Filed under Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s