SSRS Performance Improvement II – Avoid Data Filter at Report Level

Again, I would like to share another real life example in SSRS performance improvement. Obviously, in some complicated reports, my suggestions in SSRS Performance Improvement – First experience are unable to help.

So, I will show how to avoid filter in SSRS report design in this post. This is because data filtering at report level in SSRS has a significant impact on performance.

When possible, filter the data at the query level using a WHERE clause because the database is better suited to filter data. There is no gain in pulling all of the data from the database and later filtering it at the report level. When you don’t have any other option and you can’t modify the dataset query then you could use dataset filtering at the report level (i.e. you have a report containing more than one matrix/list/tablix/chart and they share the same general dataset), but the outputs must be filtered differently (i.e. one Tablix is filtered on the customers and the other Tablix is filtered on the products). Link

Real experiment:

With the filter in the tablix properties, my report loading time is 3-4 minutes; after removing that filter, the report loading time is reduced to 1-2 minutes.

Case Scenario: There are 2 parameters in the reports.

  • @StatementDate
  • @DateType – ShipDate [S], OrderDate [O]

CCBFRM parameter

The chosen date type’s date should be compared to the selected statement date, in order to display the desired result.

Initially, I have a filter in the tablix as shown in the screenshot below:

CCBFRM tablix filter

To increase the performance, I have fine tuned my query as shown below:

SELECT * FROM [AdventureWorks2014].[Purchasing].[PurchaseOrderHeader] T
where 
T.OrderDate <= case when @DateType = 'O' then @DateStatement else getdate() end
and
T.ShipDate <= case when @DateType = 'S' then @DateStatement else getdate() end

The DateType is passed to the where clause in the query with case scenario. This simple step has reduced the loading time to half. Yeah!!! However, I know my report would need more improvement, I appreciate if anyone of you has a better suggestion.

Please feel me to email me via Contact if you would like to get my sample.

Just try it yourself!!!

Advertisements

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