Book Excerpt

Reporting Services: Options and components to consider

Rendering reports

Before you configure report distribution, you need to know which rendering clients can accommodate large documents. The recommended format is Adobe Acrobat Reader (PDF), but you can choose from any format that supports pagination. You can specify the format when you define how the report is distributed.

Distributing reports

You can distribute reports via subscription — an important consideration for working with large reports. Through a subscription definition, you control how the report is distributed and rendered. You can use either a standard subscription or a data-driven subscription to deliver the report. You can also configure your subscriptions to be rendered as a PDF in a file share delivery. Use a desktop application to work with the report after it is generated. Set permissions on the file share to determine who can view the report. Note that after the report is on the file share, it is no longer controlled or secured by Reporting Services.

Using parameters and filters

Because the full set of data is retrieved and then filtered on the report server, the report may not perform as well as a report that filters data at the source using query parameters.

I note in Chapter 7 that for any report a query must be executed and the dataset regenerated each time you change a parameter that modifies the SQL query. It is possible to get better reporting performance by pulling a larger set of data into the dataset and then filtering the rows that will appear in the report.

Using snapshots

When you request a report, Reporting Services merely retrieves and renders the snapshot. You then see the data and layout that were current for the report at the time the snapshot was created. As I mention in Chapter 10, using a snapshot approach helps to improve the overall performance of your reporting environment in a number of situations:

  • Providing multiple people with access to the same data at the same time. For example, finance, marketing, and sales want the monthly financial reports when they are produced. You can specify that financial reports be set up as snapshots that execute each month.

  • Preventing arbitrary report execution. For example, an invoice report requested during business hours slows overall system performance. In order to alleviate the system load and avoid inconsistent results, you can execute the invoice report as a snapshot every evening.

  • Controlling long-running reports with queries that take a long time. For example, the weekly sales report runs a long time and each salesman wants to know results the first thing Monday morning. You can specify that the weekly sales report run every Sunday evening so that it's ready to go on Monday morning.

  • Using filters for greater flexibility. Changing the filter values filters only the current snapshot of a particular report.

    Caching in on performance

    Caching is a performance-enhancement technique. To enhance report server performance, you can preload the cache. To preload the cache with a collection of parameterized report instances, you create a data-driven subscription that uses the Null Delivery Provider. Preloading the cache is achieved through a specialized rendering extension called the null rendering extension.

    When you specify the Null Delivery Provider as the method of delivery in the subscription, the report server targets the report server database as the delivery destination.

    This feature is especially useful if you want to cache multiple instances of a parameterized report in which different parameter values are used to produce different report instances. Note that you can only specify query-based parameters on the report. In contrast with other delivery extensions, the Null Delivery Provider does not have delivery settings that you can configure or drive through a subscription definition.

    The previous tip was excerpted from Chapter 14, 'Optimizing Report Performance,' from the book "Microsoft SQL Server 2005 Reporting Services for Dummies" by Mark Robinson, courtesy of Wiley Publishing. Click here for the complete collection of book excerpts.


    This was first published in July 2006

    • Join the conversationComment

    Share
    Comments

      Results

      Contribute to the conversation

      All fields are required. Comments will appear at the bottom of the article.
      Back to top