There are several techniques to optimize report processing. One of the solutions is to enable report caching. When you enable this feature in SSRS report manager (as described below) the data are cached to the Reporting Services Temporary database (see queries below) Reporting services database. There are few possibilities of how to set the caching strategy, from planned approach to the solution with expiration cache. But, be aware that in case the user changes report parameters, the stored cache will not be used and there will be new data cache created. Keep it in mind when you set the caching strategy.
Go to the report manager.
Go to report Properties click on right arrow right to the report name and select properties form menu.
Go to Processing Options section. And set Cache a temporary copy of the report. Expire copy of report after a number of minutes: and put time you would like to hold the cache in database.
When you choose scheduled strategy. Expire copy of report on the following schedule or Render this report from a report snapshot, schedule has to be set. You have to fill the data on picture bellow. Be aware the SQL agent has to be running to use scheduling feature.
After setting property for Report Execution Cache new record is added to execution cache. You can see in database that there is time when cache will expire. After this time the record disappear from the database. (not immediately but after few seconds minutes asynchronous mechanism delete it ).
SELECT * FROM ReportServer.dbo.[Catalog] c JOIN [ReportServerTempDB].dbo.ExecutionCache ec ON ec.ReportID=c.ItemID
Change report parameter.
You can see that you record what inserted into Report Execution cache.
