Configuring Ad Hoc Cache

Facts about the Ad-Hoc (non OLAP) cache

Cache for non OLAP ad-hoc analysis features the following:

• Every query generated by the ad-hoc analysis engine returns a result set that goes directly into the cache where it is further manipulated
• Every query is analyzed to determine if there is a result set for it already in the cache or whether the query needs can be executed against the data source
• Every new query result set gets added to the cache, there is no limit on the number of result sets in the cache
• It is possible to trigger OOM if the cache size is too small
• There is currently no setting limiting the total size of the cache (not true as of Jaspersoft 5! see ehcache config)
• A query result set stays in memory until evicted
  • There are two configuration settings defining the eviction algorithm
  • A result set gets evicted if there was no activity on it for at least 20 minutes (a default setting)
  • A result set gets evicted if stays in cache for at least 60 minutes

Filtering, Sorting and Calculated Fields with Ad-hoc Cache

Filtering is currently done in SQL, which means that we push the filtering work down to a database rather than doing it in the cache

• There is an internal setting allowing to do filtering in memory but it is turned off because filtering in-memory turns out to be slow. If you want to experiment with this find the property applyQueryFilterInMemory in applicationContext-adhoc.xml. Note: In v4.7 and beyond, the property is found in applicationContext-adhoc-dataStrategy.xml.

Calculated fields currently work in memory only for topics. If a calculated field is defined in domains then it is executed through a SQL statement. This effectively gives user an option of pushing calculated fields operations down to a database.

Sorting is done in SQL, aka in the database but if the entire result set is in memory already then sorting will happen in memory

Notes on Domains and Topics

• Ad-hoc operations on domains: SQL is generated in its entirety allowing for greater flexibility and performance optimization
• Ad-hoc operations on topics: SQL is defined as a parametrized query. Only SQL that is already defined operates on a full result set in memory
• there is an option in topics to do query generation (as a nested query) or a derived table approach

Note on Ad-hoc Cache Sharing

Ad-hoc cache is shared for a given domain. It means that multiple users doing ad-hoc analysis using the same domain will be sharing the cache.

Configuring the Cache Timeout

Each dataset cached by Ad Hoc has a maximum time that it stays in the cache:

• If a dataset is not used, it is flushed from the cache after 20 minutes.
• If the data set is being used, it is still flushed after 60 minutes.

You can change this behavior by editing these properties in the /WEB-INF/applicationContext-datarator.xml (in v3.7.1 and earlier check /WEB-INF/applicationContext-adhoc.xml):

<!-- defaultTimeoutMinutes: the absolute longest time in minutes that a dataset can live in the cache -->
<property name="defaultTimeoutMinutes" value="60" />

<!-- defaultUnusedTimeoutMinutes: the longest time a dataset can stay since the last time it was used -->
<property name="defaultUnusedTimeoutMinutes" value="20" />

Set these values higher to increase the time a dataset can stay in the cache; decrease them to have JasperServer refresh the result sets in the Ad Hoc cache more frequently. Obviously, this may impact performance when reports are run.

Settings Exposed in the UI

Note: These settings are not persistent, you must redo them after a restart, to make them persist edit the XML files in WEB-INF.

• Ad Hoc Dataset Row Limit
  • there is no visual indication when result set exceeds the maximum rows setting
  • once result set reaches the threshold defined by the setting it will throw an exception
  • a partial query result set execution is not supported at the moment
  • Also see: Ad Hoc Row Limit

• Ad Hoc Query Timeout
  • if there is a long running query reaching the timeout threshold it will throw an exception
  • a timeout value of zero means this query is always re executed against the data source

• Optimize Queries for JDBC-based Reports
  • Selects grouping, sorting, and aggregation of queries for JDBC-based reports. Otherwise, the queries run unaltered in memory.
• Optimize Queries for Domain-based Reports
  • Selects grouping, sorting, and aggregation of queries for Domain-based reports. Otherwise, the queries run unaltered in memory.

Disabling the Ad Hoc Cache

If your solution has very quickly changing data and you rely on having the most current information, you may wish to disable the Ad Hoc Cache entirely. In order to disable the cache, simply set both values to "0". This will ensure that the cached results are never used.

Note: The clean-up thread time setting in this file should be left to allow clean-up on a regular interval and optimize the impact on system performance and memory utilization.