I was recently tasked with diagnosing a slow performing report in Microsoft's SQL Server Reporting Services (SSRS). The report was intended as a dashboard with multiple charts and tables showing various trending business performance metrics. Conceptually, the report was sound. The charts and tables showed exactly what the business users required. The problem was the dashboard rendered itself slower than the business desired.
My investigation showed the single dashboard viewed by users was actually multiple subreports rendered together as a single SSRS report.
SSRS supports the inclusion of subreports in any SSRS report; a subreport is simply an independent SSRS report embedded into another SSRS report. In Visual Studio BIDS, you simply drag the Subreport component from the SSRS Toolbox to the Design tab and complete the Subreport Properties. Using a subreport is easy.
As with most design decisions, usage has pros and cons, the implications of which should be considered before deploying any solution to production.
As pointed out above, an SSRS subreport simply leverages an independent SSRS report. The multiple reports used in the dashboard I faced were developed and tested independently. Each of those reports passed data quality and performance testing before being later combined as subreports on a single main report. Project managers like parallel efforts. Developers like reusing objects.
Although rendering as a single main report, SSRS executes each subreport independently. In my case, business requirements called for the multiple subreports to be kept in synch as users changed report parameters. Users did not want a chart showing 6 months of data for one client next to a table showing 12 months of data for a different client. In order for the subreports to remain in synch, a developer had to ensure all of the subreports accepted and applied the same filtering criteria uniformly. Failure to keep each table and chart in synch would mean dashboard users see inconsistent results. Suddenly, developing and testing the separate reports was not so independent.
Because SSRS treats each of the subreports independently, SSRS independently requests data for each subreport. Using the SQL Profiler, I saw separate queries submitted sequentially each time a user viewed the dashboard report. Since the different objects on the dashboard were kept in synch, each subreport requested almost identical data. That means multiple queries, executed sequentially, returning comparable results. No wonder the dashboard rendered results significantly slower than each of the independent reports.
Once I understood how the dashboard charts and tables were integrated, I realized the performance problem was caused by the dashboard design rather than the SQL Server database or the SSRS platform. Yes, each subreport had been independently performance-tested, but no one adequately considered how those independent subreports could be optimized together on the dashboard.
Instead of utilizing multiple SSRS subreports, I redesigned the dashboard to present multiple charts within a single SSRS report. I combined the multiple object-specific queries into a single query providing data to all those objects. That single query took almost the same execution time as each of the earlier object-specific queries. To avoid redundant efforts, I leveraged the earlier work by copying the charts and tables from the independent SSRS subreports into the single SSRS report.
In the end, business users saw the results they wanted in the format they wanted in the response time they wanted. Yes, we lost the flexibility and reusability of earlier independent reports, but the decision is now based on the overall pros and cons of our design options.
Of course, this scenario does not mean all SSRS subreport usage is bad. Parallel development and testing remain good. Object reuse remains good. If the charts and tables in my scenario had not presented the same business data, I likely would have retained the subreport design and looked for some other way to optimize performance. In fact, as long as the subreports do not select the same data, embedding subreports in an SSRS report is a reasonable design practice.
As successful business intelligence developers, we need to understand business user intentions and recognize the trade-offs of design decision pros and cons in order to optimize each solution.