In this article, I will show you how to use caching for SSRS reports. But before that go through below lines which will help to understand the concept of caching in sql server reporting services.
A cached report is a saved copy of a processed report. Cached reports are used to improve performance by reducing the number of processing requests to the report processor and by reducing the time required to retrieve large reports. They have a mandatory expiration period,
A report server can cache a copy of a processed report and return that copy when a user opens the report. To a user, the only evidence available to indicate the report is a cached copy is the date and time that the report ran. If the date or time is not current and the report is not a snapshot, the report was retrieved from cache.
Caching can shorten the time required to retrieve a report if the report is large or accessed frequently. If the server is rebooted, all cached instances are reinstated when the Report Server Web service comes back online.
Caching is a performance-enhancement technique. The contents of the cache are volatile and can change as reports are added, replaced, or removed.
To enable caching for a report, go through below steps :
1. First of all open Internet Explorer and go to Report Manager URL which is something like below:
http://bhushan-pc/Reports2012
Your internet explorer tab looks like below :
2. Click on your SSRS project. In my case it is Start SSRS. So now it will show you list of reports which are deployed on your report server.
3. Now click on down arrow on the report which you want to subscribe and selectManage as shown in below screen shot.
4. Then select Processing Options from left pane and you will see screen shown in below screen shot. In that screen, you have three different choice.
Either you can set time in minutes
or you can defined a schedule as shown below.
5. Then click on Apply button. So we have done with Caching SSRS Reports.
Main purpose of Caching SSRS Reports is to improve the performance. To see the effect of Caching, execute following query.
1
2
3
| SELECT ITEMPATH,USERNAME,PARAMETERS,TIMESTART,TIMEEND,TIMEDATARETRIEVAL, TIMEPROCESSING,TIMERENDERING,SOURCE,BYTECOUNT,[ROWCOUNT]FROM EXECUTIONLOG3 |
Here you can see that it will not take even 1 second to retrieve data. Similarly you can see time difference in TIMESTART & TIMEEND.
6. You can also create Cache Refresh Plan which create a schedule for preloading the cache with temporary copies of data for a report. A refresh plan includes a schedule and the option to specify or override values for parameters.
To create a Cache Refresh Plan go to Cache Refresh Options and click on New Cache Refresh Plan.
7. In that screen give details as shown in below screen. Then click on OK button.
you can also check the history of Cache Refresh Plan.
Congratulations! We successfully completed use of Caching SSRS Reports.
