Troubleshooting
Problem
Improving the performance of reports by using cached data when running a report.
Resolving The Problem
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, you should 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 of your current session and reused the next time you run the report. The queries are kept in the cache for each user. The cache is cleared when the report consumer exits the reporting tool and returns to the portal or when the report server times out the session, typically after five minutes of inactivity.
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 Framework Manager and republish the model. By default, this setting affects all reports that use this model.
Query Reuse in Report Viewer or Cognos Viewer
If you want a report to use a different setting than the model, you can do this in Report Studio. 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 will not affect other reports.
Reusing Cached Data in Query Studio
Query Studio reuses cached data under various conditions.
If query reuse is turned on in the model and the action can be satisfied by a subset of the cached data set, the report uses the cached data. For example, changes to the report such as adding a filter or removing a column, may change the report data but the request can still be satisfied from a subset of the cached data.
If query reuse is turned off and the action can be satisfied from the cached data set without modifications, the report will still use the cached data. For example, changing the report format will use previous data set even if query reuse is turned off. This is known as cursor reuse. Cursor reuse is used when the cached data can satisfy the request without modifications.
Reports that were authored in Query Studio always use the same setting as what is specified in the model. If the model has query reuse turned on, the report will attempt to use the cached data.
Deciding Whether to Use Query Reuse in Your Environment
Before deciding whether or not to turn query reuse on, you should 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 are run in batch 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.
Steps:To Enable/Disable the cache in Framework Manager:- Open up your model in Framework Manager
- Click on "Project" in the menu, and select "Edit Governors..." This will open the Governors Dialog.
- Check the box beside "Allow usage of local cache" to enable/disable the default use of local cache.
To Enable/Disable the cache in a specific report:
- Open the report in Report Studio
- Hover the mouse over the "Query Explorer" until the explorer slides open, then click "Queries" at the top of the list, or click "View" in the menu, and select "Queries".
- Single click the query for which you wish to set the use of local cache. Its properties should appear in the Properties window.
- Find the property named "Use Local Cache" and set it to "Yes" or "No" depending if you want to enable or disable query caching.
- Repeat for any other queries.
Related Information
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21344305