Introduction
In the Managing the Report Content Using Report Manager: Part I we saw how to go about setting the security for the Report Manager report items. In this article we will be focusing on caching SSRS reports.
Caching
Caching is when a copy of the processed report after the execution is saved into into ReportServerTempDB for faster retrieval. This is beneficial when a particular report takes a long time to create and is accessed multiple times. In this scenario if the report is cached then the execution time is drastically reduced as the report’s result set is already stored in the cache and is simply returned.
Since we will be using the ReportServerTempDB for caching, it is to look at the difference between the ReportServer and ReportServerTempDB.
ReportServer Database Vs ReportServerTempDB
ReportServer:
1. Stores information about the report schema, report property, data
sources, parameters, stores the folder hierarchy, report Execution log.
2. Always exists until changes are made to the RDL Schema
3. Helps to access the structure.
4. Data always exists during SSRS service restarts
ReportServerTempDB:
1. Stores only the cached copy of the report data.
2. Expires based Expiry settings.
3. Helps to improve the performance of report execution as it is loading
data from cache.
4. Services restarts will clear the Temp Data
Note: You need to have a data source with stored credentials on the report server in order to implement caching. If you have windows credentials then it will throw the below error:
How to Implement Caching in SSRS?
Implementing the caching in the SSRS is really very simple. If you are using SSRS 2008 R2 then you need to just click on the report on the dropdown for which the caching needs to be performed and click manage:
The following menu bar will be displayed on the left pane from which we are going to focus on caching options.
Click on the processing option. It will show a list of options which are as follows:
- Do not cache temporary copies of the report.
Caching Options
- Cache the temporary copy of report for limited amount of time (only in mins).
- You can schedule the cached report to be available in the system for a specific duration after which it will automatically expire
- Render the report from the report history snapshot.
- You may use the system’s default setting (which is defined in web.config file). This is applied to reports deployed on the report server. If you wish to change it you may edit the web.confg file.
- Report may never timeout.
- You may specify the report time out (only in mins) for this specific report only.
Click apply to save your settings.
How to implement caching in reporting services?
Consider a production scenario where a particular MIS report takes a long time to process and may even time out. Obviously there is a huge performance issue with this kind of report. In this scenario you can either fine tune the query or you can cache the report for faster retrieval. The below query takes more than 1 min for execution which may be unacceptable in the live production scenario. I have purposely applied a cross join to demonstrate the caching feature of ssrs.
SELECT pc.EnglishProductCategoryName, psc.EnglishProductSubcategoryName, p.EnglishProductName, frs.UnitPrice, frs.ProductStandardCost,
frs.TotalProductCost, frs.SalesAmount
FROM DimProductSubcategory AS psc inner JOIN
DimProduct AS p on psc.ProductSubcategoryKey=p.ProductSubcategoryKey inner JOIN
DimProductCategory AS pc on pc.ProductCategoryKey=psc.ProductCategoryKey cross JOIN
FactReSellerSales
In order to perform caching for limited number of minutes follow the below steps:
1. Right Click on the Product Sales Analysis report. Go to Manage->Processing options
2. I have selected the default option of 30 mins but as you can see its a free text cell and you can input any number of minutes you want the report to be cached for. Click Apply.
3. At first as you can see the the report takes the same amount of time for execution as the default running time of the query as the reason being the report is also being cached while its being processed.
4. When you run the report for second time the report executes much faster than the previous instance obivously because this time it is being rendered from the cache.
5. As you can see we have an option of scheduling the caching duration which will expire after the specific amount of time. You can schedule the report anywhere from hourly to monthly basis. Also the option of the ‘once’ is given if the report needs to be processed and expired on a specific day.
6. You also have an option to render the report from the snapshot option for which you need to specfy the schedule as when this snapshot needs to be taken. A snapshot never expires but is replaced with newer version of the report on the next scheduled execution.
7. I can schedule a cache refresh for my report on the schedule of hourly,daily weekly or monthly basis. A cache refresh plan can help you preload the data on the scheduled basis and ensure fresh data is being served.
8. Since the cache refresh plan is a scheduling operation ensure that SQL Server Agent is up and running.
9. In this demo I have kept the default settings but you can customize it as per your requirements.You can also have a shared schedule in place and use the same caching throughout the process.
10. You can also specify caching on parameters defined in you dataset and thus a new report will be cached for different sets of parameters. Since I have not defined parameters the option is unavailable for me.
11. After the cache refresh plan has been added your screen will look something like this:
As you can see you may have an option of creating the new plan from the exisiting one in which all the existing plans’ settings will be made available to you or you can always create a new cache refresh plan.
In the next article we will see how to manage subscriptions and scheduling of the reports to be delivered into your inbox. ]]>