Share via

Report Performance Optimization Tips (Subreports, Drilldown)

  • Article

In a previous posting, I explained the new ExecutionLog2 view in Reporting Services 2008: how to utilize it to measure report execution performance, identify report candidates for optimizations, and verify the impact of the latter.  I also included two tips at the bottom of that posting, that I want to call out explicitly as a separate posting. 

Subreports

One of the aspects I usually investigate when optimizing reports is the usage of subreports.  Subreports provide a convenient way to reuse report designs, but it may make sense to refactor the report design to lift the subreports contents into the main report.  Not only does this frequently result in improved performance, it can also provide better control over rendering behavior.

Btw, this related blog posting explains how one can quickly find out which published reports reference subreports.

Splitting large drilldown reports into parameterized drillthrough reports

When reports use large datasets, I have sometimes seen the usage of drilldown group hierarchies (toggles) to provide an initial overview to the report consumer, and enable dynamic drilldown to interesting areas. 

What may work really well for those kind of reports is to change the original report to only include data till e.g. the third level of drilldown (and thereby reduce the amount of data retrieved and processed by the initial report, and improve response time). 

Then at the innermost drilldown level of the main report, create drillthrough actions with parameters based on the current group values to another report.  The drillthrough target report provides further details and drilldown capabilities, but only has to retrieve a small subset of the original data.

Comments