Reports Dog-Slow? Use these tried and true techniques to make your reports BLAZING fast

Tricks from the Trade:  Tips and Advice from a seasoned Microsoft Business intelligence Developer

Developing numerous reports over the years, I've encountered situations where despite extensive fine-tuning, I just couldn't get it to run at an acceptable speed.

One of the tricks I have utilized in this situation is to setup a report to refresh when no-one is looking (usually in the wee hours of the morning) and make sure it does not refresh later in the day when people are actually viewing it.

The report opens almost instantly and the user perceives it to be quick, little knowing that the report was a total resource hog when it actually ran first thing in the morning.

The users don't have to to twiddle their thumbs when they open the report and the report server is not a hit with an expensive query every time it is run - so this kind of setup is beneficial on many levels.

Depending on your reporting platform, setting up reports to run this way can take some creative planning.  This is accomplished quite easily with SQL Server reports (SSRS) but I have done this with Crystal Reports, Excel and MS Access as well.  

If you are using SSRS, there are easy to configure processing options to setup report snapshots, caching or subscriptions  - or a combination of all of these features.

Report snapshots are scheduled times when the underlying data for a report is fetched and saved with the report, so that virtually no processing takes place afterwards.  When the user opens the report after the snapshot has run - the data is already there and the report is rendered instantly.

Caching is similar to a report snapshot in that the data is fetched and saved with the report. But, instead of the report refresh being scheduled, the caching occurs whenever the report is open for the first time.  So, the first user takes a hit in this situation but subsequent users can open the report instantly.  (There is a scheduling component involved with caching as well, because you have to specify when the cache expires, but I will go into this in another post.) Snapshots are usually preferable to caching because none of the users have to suffer through report refreshes with the latter option.

Subscriptions come in handy if you MUST include user parameters in your report.  Subscriptions allow for parameters so long you can specify default parameter values.  Schedule the subscription to refresh the report as early as possible and configure the subscription to email the report to the users, or have the subscription export the report to a PDF or Excel file. This accomplishes the same goal as snapshots and caching, so long as your users know to only open the exported report file, not the original SSRS report.  

A note about user parameters.  In most cases I have found that these parameters are predictable enough that they don't really need to be specified by the user. For example, if the report requires a date, and that date is always yesterday, you can calculate that date it the report itself without requiring parameters.  If you are able get rid of parameters altogether, then snapshots or caching are better options than subscriptions.

About the Author:
Noah Meyer, aka The SQL Fix-It Guy, is a Denver-based database developer, trainer and coach and has spent the past two decades helping leading organizations (banks, hospitals, energy companies and government agencies) improve their reporting capabilities by fixing and fine-tuning their customized software implementations.