Windows Azure SQL Reporting has a little known report caching feature. When enabled, you can see performance improve for some reports by up to 95%! 

Here’s a quick sampling that shows how much caching can effect report rendering performance:

 

 

The above graph shows the time (in seconds) it took to render a pretty large report hosted in SQL Reporting (in this sample I rendered it in a loop once every 5 minutes). Before enabling caching, the time to render the report was fairly steady, around 19 seconds. Notice how the report rendering time dropped to around 2 seconds as soon as caching was enabled!

 

How does caching works?

To understand how caching works, let’s take a closer look at the example shown in the above graph.

In that sample, the report we rendered took about 19 seconds to render, and its execution log showed that most of the time was spent on Data-Retrieval. When we enabled caching, we configured the duration for 10 minutes. Now that we understand the execution profile of this report, let’s zoom in on the latency graph to see what really happened once caching was enabled:

 

Given that we render the report once every 5 minutes, and the report stays in the cache for 10 minutes, you can see that after every slow render came 2 very quick renders.

 What happened was that the slow render (which before enabling caching was our usual speed) also cached the report data, and the next few render attempts took the report data from the cache (resulting in the data-retrieval time for the report reducing to almost zero). When the cache expired (after 10 minutes) the next render was slow again, since it included refreshing the cache of the report.

 

Will users getting a cached report see old data?

Yes. This is why caching is not enabled by default, and the cache expiration time can be configured separately per report. If you know the data in your report gets updated only once a day, you can configure a long cache. On the other hand, if your report data gets refreshed frequently and you want to always view the up-to-date data when rendering the report, then you shouldn’t cache this report.

 

Does caching work per user session?

No. If you cache a report, all the users accessing that report will get a cached copy.

 

What happens if my report has input parameters?

This is the tricky part. Report parameters do effect caching, however only in cases where these parameters affect your report’s DataSet.

 When creating the cache, the report server calculates a hash of all the parameter values of your DataSet, and keeps a cache per each parameter combination. This means that if your report had a DataSet called GetAllOrdersForCustomer, and that dataset had an input parameter called CustomerName, whenever rendering the report for a different customer name, a new copy of the cache will get generated.

This is a pitfall to watch out for. If your report is usually executed with different input parameters, then caching will in fact lower your performance, because every request will result in the cache getting created again.

 You can avoid this if you plan your report right. For example, if your report DataSet has an input parameter called StartTime, and you configured the default value StartTime in your report to be the expression Now(), you can change your report to better support caching by giving StartTime the default value of Today() instead.

 

So why is this great feature hidden?

It’s not really hidden, but rather poorly documented. Caching was a feature which you could usually enable in the on-premises version of SSRS by using the report manager, however since the report manager isn’t available with SQL Reporting, the only way to enable it is using a programmatic API call.

 

How do I enable caching?

By using the ReportingService2010 API, simply execute the following code:

 

ReportingService2010 client = new ReportingService2010();

client.Url = "https://<YourServerUrl>.reporting.windows.net/ReportServer" + "/ReportService2010.asmx";

client.CookieContainer = new CookieContainer();

client.LogonUser("YourUser", "YourPassword", null);

client.SetCacheOptions("/YourReportNameAndFolder", true, new TimeExpiration { Minutes = 10 });

Just change YourReportNameAndFolder (notice the “/” prefix) and the TimeExpiration minutes to fit your needs. Similarly, to disable caching just call SetCacheOptions again for your report, and change the second parameter from true to false.

 

What about snapshots? Does SQL Reporting also support snapshots?

No. See http://msdn.microsoft.com/en-us/library/windowsazure/gg430132.aspx#DetailedLevel

 

Further reading

To learn more about caching and how it affects performance, see:

http://msdn.microsoft.com/en-us/library/ms155927.aspx

http://blogs.technet.com/b/rob/archive/2010/02/11/caching-ssrs-reports-for-performance.aspx

http://www.databasejournal.com/features/mssql/report-caching-in-sql-server-reporting-services-2008-r2.html