Performance issue while Grouping in SSRS Reports

In one of our projects the report was taking too much time to load the report. When we analysed the issue we found that we are unnecessarily loading all the records into local reporting server when we need only top level data after applying grouping.
To explain this I am taking a simple example here but in real time there may be complex reports but with little analysis we can find the way to increase performance. Let’s take orders data, and we need to get a report of sales details that in various states.


When we write the query as below to get the details and apply the grouping in the SSRS report.

SELECT OrderID, State, Price, Product FROM SalesDetails

When there is more than 100,000 records in sales and when we run the report it will first fetch the 100,000 records to the report server and then applies the grouping. Assume if the network bandwidth is 1Mbbs it will take at least 10secs assuming each record of 100 bytes.
Instead of this we can use the grouping within the select statement to get only the required data –

SELECT State, Sum(Price), Count(1) FROM SalesDetails GROUP BY State

It will return only those number of records as sales.


But in real time tables may involve joins also. For those use nested select statements like –

SELECT t.State, Sum(t.Price), Count(1) FROM
( < your complex query with joins and other conditions > ) t
GROUP BY State


Lesson is to CONSIDER BANDWIDTH ALSO WHILE DEVELOPING REPORTS.