Introduction:
I recently worked on a quick SSRS (SQL Server Reporting Services) project with a client that had a need to be able to query large datasets (potentially over 300,000 rows by 30 text columns wide). The report needed to be very dynamic, meaning that the report would need to allow the end user to run the report to return all of the data, or if they chose, filter on any of the columns to limit the end result. Not only this, but the query to pull the data needed to join to almost as many tables as there were columns in the report (a bit of an over exaggeration but we have all been there before).
The true problem with the report is that it would time out when running it from the web browser within SharePoint Integrated mode. The strange part is that the query would run in Management Studio in a couple seconds, and within BIDS (Business Intelligence Development Studio) within about a minute…which in my opinion was still way to long for a report that would be run often by users as an ad-hoc query tool. What could we do to improve the performance of the report?
Suggestion:
Whenever you run into a situation like this, use SSIS (SQL Server Integration Services) to write a quick package which can pre-aggregate the data in some type of reporting database. In this scenario, that was not possible since we needed the data to be available at the most granular level. However, even though we couldn’t pre-aggregate the data, we were able to flatten it within a newly created reporting database table.
Essentially what we did was author an SSIS package that ran the original report query to dump the data into a new table local to the installation of SSRS. We were allowed this solution because we had a server that was underutilized that could handle the extra capacity of a new reporting database as well as the strain from having SSRS hit (query) the instance of SQL installed on the box. By doing this, we removed the need for all of the INNER and OUTER joins within the query, and allowed the report to only hit one table in order to return any combination of results from the large dataset.
This scenario worked out well, the SSIS package ran in a matter of seconds and was scheduled to pull the data each day in order to make sure that the data for the report was always refreshed. The SSRS report runs super fast (matter of seconds) and users are able to execute the report at their own will quickly and efficiently.
