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
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.
- @DateType – ShipDate [S], OrderDate [O]
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:
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!!!