SSRS Performance Improvement III – Avoid Dynamic Grouping at Report Level

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”

CCBADG grouping parameter.png

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!!!

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