LearnNowOnline

Blog

SSRS 2012: Preview Performance for Report Builder

Excerpt by Don Kiely | May 09, 2014

When you work in Design view in Report Builder, you are not working with real data, even if you created a data set and attached it to a data region. Report Builder uses that data set design to discern the schema for the data, but uses only a representation of that data. That's why you'll want to preview a report repeatedly as you design the report so that the actual data looks as you envisioned it. When you click the Run button in Design view, Report Builder reads the actual data from the data store and renders the report so you can view it with actual data.

It connects to the data source you specified and caches it, then combines the data and layout to render the report. You can switch between design and preview as often as necessary. This is convenient for developing a report, but it can be a painfully slow process. If the data set uses a complex query that takes time to execute in a database, for example, you might have a significant wait for the report preview. In older versions of Reporting Services, you just had wait patiently. However, newer versions of Report Builder greatly enhance the report preview process by using edit sessions when you're connected to a report server.

The edit session creates a data cache on the report server that it retains for your next report preview. This way you have to wait for the data only once; subsequently, the report preview appears almost instantaneously. As long as you don't make any changes to the data set or any report changes that affect the data, report previewing uses the cached data. If you ever need to use fresh data, you can preview the report and click the Refresh button in the Report Builder's preview toolbar, as shown in Figure 1.

Figure 1. Refresh button in preview mode in Report Builder.

Report Builder creates an edit session the first time you preview the report; the session lasts for two hours by default, and resets to two hours every time you preview the report. The data cache can hold a maximum of five data sets. If you need more or use a number of different parameter values when you preview the report, the data cache may need to refresh more often, which slows preview performance. You cannot access the underlying edit sessions that Report Builder uses to enhance preview performance, and the only properties you can tweak to affect preview behavior are the length of an edit session and the number of data sets in the cache. But actions you take can affect whether Report Builder is able to use the cached data, so it is helpful to have a basic understanding of what affects the edit session's use of cached data.

TIP: To change the cache expiration timeout or the number of data sets the cache stores, use the Advanced page of the Server Properties dialog box for the Reporting Services instance from Management Studio.

The following changes cause Report Builder to refresh the cache, which causes a slower report preview:

• Adding, changing, or deleting any data set associated with the report, including changes to its name or any properties.
• Adding, changing, or deleting any data source, including changes to any properties.
• Changing the language of the report.
• Changing any assemblies or custom code in the report.
• Adding, changing, or deleting any query parameters in the report, or any parameter values.

This list suggests that Report Builder refreshes the cache conservatively, that is, any time there might be an effect on the data used by the report. But changes to the report layout or data formatting do not cause the cached data to refresh.

TIP: Adding or deleting columns in a table or matrix does not refresh the cache. All of the fields in a data set are available to the report, whether you use them or not, so these actions do not affect the data set.

This post is an excerpt from the online courseware for our SSRS 2012 Developer course written by expert Don Kiely.