Improving performance by reusing cached data when running a report

When you run a report, the query request is sent to the database and the result set is returned. After the initial report execution, you may decide to make changes to the report. Often, the report can be created without querying the database again. To take advantage of this, turn the query reuse feature on.

When query reuse is turned on and you run a report for the first time, the query is stored in the cache on the report server. Also, some data source resources may not be available until the transaction using them is released. The current default time-out is 60 minutes. If certain database activities involve modifying database objects, you must wait for the time-out period to be completed, or you can disable query reuse for reports.

The first time the report is run and the cache is created, the response time may be slightly negatively impacted. The performance improvement is realized by the report consumer on each subsequent report execution, when the response time is improved by as much as 80%. This performance improvement occurs because the report does not have to re-query the database. In addition to this, reduced queries to the database yields improved overall system performance, which positively impacts all users.

Query reuse can be set on the model or on individual reports. To specify that all reports using a particular model should use cached data, enable the Allow Usage of Local Cache governor on the model in IBM® Cognos® Framework Manager and republish the model. By default, this setting affects all reports that use this model, including analyses that are run as reports in IBM Cognos Viewer.

Query reuse in IBM Cognos Viewer

If you want a report to use a different setting than the model, you can do this in IBM Cognos Analytics - Reporting. In the Properties pane, change the Use Local Cache property. Set the property to No if you want to always execute the query. Set the property to Yes if you want to use cached results. If you want the report to use the same setting as the model, change the setting to Default.

Changing the Use Local Cache property for one report does not affect other reports.

Deciding whether to use query reuse in your environment

Before deciding whether or not to turn query reuse on, consider the following:

  • If most report consumers run reports interactively but run them only once, you may not experience a high level of performance improvement by caching data.
    Note: Regardless of the query reuse settings, reports that run in batch mode do not cache data.
  • The size of the cache may impact scalability. For example, if a report has a large result set, the cache will also be large. This should be taken into account when sizing and configuring your server environment.