CODEWITHSUNDEEP
sqlreporting-services
Sagar S
Sagar S

Reputation: 41

SSRS Caching and/or Snapshot

I am fairly new to SSRS reports so I am looking for guidance. I have SSRS reports that have 3 visible parameters: Manager, Director, and VP. The report will display data based on the parameters selected. Initially, the report was taking a very long time to load and my research led me to creating a snapshot of the report.

The initial load of the report is really quick (~5 secs) but the parameters are set to "Select All" in all sections. When the report is later filtered to say, only 1 VP, the load time can vary anywhere between 20 to 90 seconds. Because this report will be used by all aspects of management within the organization, load time is critical.

Is it possible to load the filtered data quicker? Is there anything I can do?

Any help will be much appreciated.

Thank you!

Upvotes: 0

Views: 764

Answers (1)

StevenWhite
StevenWhite

Reputation: 6034

This is a pretty broad efficiency issue. One of the big questions is whether or not the query takes a long time to run in the database or just in SSRS. Ideally you would start with optimizing the query and indexing, but that's not always enough. So the work has to be done somewhere, all you can do is shift the work to be done before the report is run. Here are a couple options:

Caching

  1. Turn on caching for the report.
  2. Schedule a subscription to run with each possible value for the parameter. This will cause the report to still load quickly once an individual is specified.

Intermediate Table

  1. Schedule a SQL stored procedure to aggregate and index the data in a new table in your database.
  2. Point the report to run from this data for quick reads.

Each option has it's pros and cons because you have to balance where the data preparation work is done. Sometimes you have to try a few options to see what works best for your situation.

Upvotes: 1

Related Questions