Changing the cache size of dynamic queries

When a user's content (a dashboard for example) has more than 25 queries set to run concurrently, some query results may fail to load. To mitigate this issue, you can increase the values assigned to the queryReuse parameter.

About this task

Dynamic Query can improve query execution by reusing previously computed results. As queries are planned, Dynamic Query determines if there are applicable results in the cache that it can leverage. Each instance of Dynamic Query manages a cache, which can hold up to 250 entries. Each user session accessing that instance can have up to 25 entries in that cache. Cached entries are removed based on either inactivity or to make space for new entries.

Potentially, dashboards may include many widgets in one or more tabs. The queries generated by those widgets may cause entries to be evicted from the cache to make room for new entries. As a user interacts with the dashboard, they may observe that some queries respond more slowly. This may be due to the number of widgets on the dashboard that they are using and how they interact with it.

Tip: Raising the cache size increases both memory usage and temporary disk space. Before you raise the cache size, review the extent to which queries are embedded in your content. For example, perhaps your dashboards can be re-designed with fewer widgets. Or maybe you can move rarely used widgets to a different tab to reduce how often they are executed.
Removing partially filled result sets from the cache

When a query is executed against a data source, the data source executes a statement and produces a result set. The data source may create locks or consume temporary space, which it releases as the result set or statement objects it is managing are closed.

In many cases, Dynamic Query executes a statement and reads all the rows returned by the data source result set. Dynamic Query can then call the methods in the data source client library to indicate that those objects are to be closed, and in turn, the data source can release any resources it created.

You may be viewing a report interactively in the viewer, and slowly page through the output and may not read all the rows. When query re-use is active, the cached result set is only partially filled, and the associated database objects is long-lived.

Should those resources impede other activity in the data source, you may need to set qs.general.queryReuse.retention.removePartialDataset to true.

In applications that use Framework Manager, you can consider setting the Cursor Mode policy to Load in Background.

Procedure

  1. Start the Administration console.
  2. Follow the steps in Setting query service properties.
  3. In step 6 of the procedure above, select Advanced settings.
  4. Enter new values for the qs.general.queryReuse.size and qs.general.queryReuse.data.threshold parameters.
    Example
    You want to make these changes to the query cache:
    • Increase the maximum number of queries per user from 25 to 30.
    • Increase the maximum number of queries in the global pool to 350.
    Enter these name/value pairs for Parameter and Value:
    qs.general.queryReuse.size
    30
    qs.general.queryReuse.data.threshold
    350
  5. From the Actions menu for the QueryService - dispatcher_name, click Start to restart the service.

Results

The query service is configured with the new settings.
Note: As an alternative to using the Administration console, as described in the previous steps, you can create an xqe.config.custom.xml file. To make the same changes as in the previous example, follow these steps:
  1. Stop the IBM Cognos Analytics service.
  2. Go to installation_location\configuration
  3. If the file xqe.config.custom.xml does not yet exist, copy the file xqe.config.xml and rename it xqe.config.custom.xml
  4. Edit xqe.config.custom.xml:
    1. Enter the queryReuse parameter and its values after the <general> line.
      <queryReuse enabled="true|false" size="number_of_queries_in_user_cache">
   <!-- resuable objects retention in seconds .-->
   <retention maxIdle="number_of_seconds" maxAge="number_of_seconds"/>
  <!-- result sets held for reusable objects. Memory is maximum per result set in megabytes.-->
   <data threshold="number_of_queries_in_global_cache"/>
</queryReuse>
      Example
      You want to make these changes to the query cache:
      • Increase the maximum number of queries per user from 25 to 30.
      • Remove query results after 3600 seconds of inactivity.
      • Increase the maximum number of queries in the global pool to 350.

      Add the following lines immediately after the <general> line:

      <queryReuse enabled="true" size="30">
   <!-- resuable objects retention in seconds .-->
   <retention maxIdle="300" maxAge="3600"/>
  <!-- result sets held for reusable objects. Memory is maximum per result set in megabytes.-->
   <data threshold="350" maxMemory="6"/>
</queryReuse>
    2. Save xqe.config.custom.xml.
  5. Start the IBM Cognos Analytics service.