Using Row Group Expressions In SSRS Causing Preview To Run Very Slow

Question

I have a stepped report that has 5 row groups. Each one uses an expression as the value to group on. The user selects a value for up to 5 different parameters and based on those parameters is how the report is grouped.

With certain combinations of parameters the report may take 2min + to run in the preview pane in Visual Studio. When I deploy the report to the SSRS server the same combinations of parameters runs in 10 seconds or less. Out of curiosity, I created a copy of the report and removed the expressions from each of the row groups and specified a field to group on. In that case it previews just as fast as when viewed on the server.

Anyone have any idea what may be going on here?

I'm currently using Visual Studio Enterprise Update 3.

Answer 1

I realize this is an older issue, but maybe this will be some help to you.

I am experiencing similar symptoms to what you have described and it comes down to using dynamic fields in grouping statements. Apparently, any other statement can use dynamic fields without issue, but groupings incur a huge performance hit.

This link outlines the symptoms and causes.

I don't know if it was in that particular link or not but I read something about how the ReportViewer control when it renders to HTML has a bug in it that only happens when the control is running under .Net 4.0 or higher. In .Net 3.5 the performance is pretty much the same as it is if you were to run the RDL in the Report Viewer application.

If you are able to do so, the quickest way to get the report viewer component to work is to have the application pool IIS is using to run under .Net 2.0 (or 3.5 if that option is there, since they're basically the same thing).

If your application uses .Net 4.0 or higher for other functionality, there isn't much I've been able to figure out, short of rewriting the report to remove dynamic references from groupings, but that significantly reduces the interactivity of the report (no expand/collapse options in table rows/columns).

For reference, I had a report, where the query took about 2 minutes to execute, and then rendered almost instantly afterwards if I loaded the RDL in SSRS. That same report rendered using the ReportViewer control in .Net 2.0 took about 2 min 30 sec to fully render. Using the exact same code, but changing the app pool to use .Net 4.0, the report has been "rendering" for roughly 15 minutes now and still hasn't come back.

Answer 2

First of all and a bit obvious, if you are running your report in a development machine it won't have the same computing power as a server. Even if you are running VS from the server it won't run as fast as a deployed report.

The cause -as you may notice- for the slow performance is the grouping expression. It seems your report is taking to long to process so check this article from a Technet post. I copied and pasted a paragraph below:

Many levels of nested and adjacent groups in a Tablix data region can affect report processing performance. Consider both the level of grouping, the number of group instances, and the use of aggregate functions which require evaluating after group, filter, and sort expressions are applied.

I recommend you try to perform the grouping in the datasource at SQL level, you can also pass parameters to the query to set the desired grouping there.

Let me know if this helps.