Part III of SSRS performance improvement! Thanks to myself for not giving up in fine tuning my report performance, the report loading time has reduced from almost 2 minutes to 10 seconds! I am incredibly happy for this great achievement, not wasting my effort of thinking squeezing rotating my brain throughout the night.
My previous post about SSRS Performance Improvement II – Avoid Data Filter at Report Level has significantly reduced the loading time by half, but the report loading time is still taking almost 2 minutes. It is fine for some users but some of the impatient users, 2 minutes report loading time is killing them!!!
So, without further mumbling, let’s start working on the SSRS report enhancement! In this post, I will show how to avoid dynamic grouping by Parameter at SSRS report level. I never realised that dynamic grouping has such a great impact on performance.
What is dynamic grouping? The report could be grouped by 3 levels depending on the selected Parameter. Please visit SSRS Report – Dynamic Grouping at Report Level for more details. I have shared the step by step guidance on how to setup the dynamic grouping at the report level.
Well, back to the topic, HOW to avoid the expression grouping at report level? we could enhance the dataset query as below:
First of all, we have to enhance the dataset query as below:
SELECT T.*, E.FirstName, V.Name as vendor, S.Name as shipname, case when @GroupingType = 'E' then E.FirstName when @GroupingType = 'V' then V.Name when @GroupingType = 'S' then S.Name end as GroupingType FROM [AdventureWorks2014].[Purchasing].[PurchaseOrderHeader] T left join [AdventureWorks2014].[Purchasing].Vendor V on V.BusinessEntityID = T.VendorID left join [AdventureWorks2014].[Purchasing].ShipMethod S on S.ShipMethodID = T.ShipMethodID left join [AdventureWorks2014].[Person].[Person] E on E.BusinessEntityID = T.EmployeeID
Then, let’s set the row group properties to group on “GroupingType”
The GroupingType parameter is passed to a new column [GroupingType] in the dataset with case scenario. If E is selected, then the column will be retrieved from FirstName of the Employee. This simple step has reduced the loading time from minutes to seconds. Yeah!!!
Last but not least, as usual, I appreciate if you could provide some brilliant ideas for me. 🙂
Also, please feel me to email me via Contact if you would like to get my sample.
Just try it yourself!!!