JBoss.orgCommunity Documentation
Teiid provides the capability to cache the results of specific user queries. When the exact same user query is submitted to Teiid, the cached results will be returned. This caching technique can yield significant performance gains if users of the system submit the same queries often.
Result set caching will cache result sets based on an exact match of the incoming SQL string and PreparedStatement parameter values if present. Caching only applies to SELECT, set query, and stored procedure execution statements; it does not apply to SELECT INTO statements, or INSERT, UPDATE, or DELETE statements.
Caching of user query results.
Scoping of results is automatically determined to be either VDB (replicated) or session level.
Caching of XML document model results.
Configurable number of cache entries and time to live.
Administrative clearing.
End users or client applications explicitly state whether to use result set caching. This can be done by setting the JDBC ResultSetCacheMode execution property to true (default false) or by adding a cache hint to the query. Note that if either of these mechanisms are used, Teiid must also have result set caching enabled (the default is enabled).
To control the preference of individual results to remain in memory or the time to live see the cache hint. If a cache hint is not specified, then the default time to live of the result set caching configuration will be used.
Each query is re-checked for authorization using the current user’s permissions, regardless of whether or not the results have been cached.
By default result set caching is enabled with 1024 maximum entries with a maximum entry age of 2 hours.
There are actually 2 caches configured with these settings. One cache holds results that are specific to sessions and is local to each Teiid instance.
The other cache holds VDB scoped results and can be replicated.
See the <jboss-install>/server/<profile>/deploy/teiid/teiid-jboss-beans.xml config file or the Console's "Runtime Engine Properties" for tuning the configuration.
The user may also override the default maximum entry age via the cache hint.
Result set caching is not limited to memory. There is no explicit limit on the size of the results that can be cached. Cached results are primarily stored in the BufferManager and are subject to it's configuration - including the restriction of maximum buffer space.
The result set cache can be cleared through the AdminAPI using the clearCache method.
The expected cache key is "QUERY_SERVICE_RESULT_SET_CACHE".
Example 2.1. Clearing the ResultSet Cache in AdminShell
connectAsAdmin()
clearCache("QUERY_SERVICE_RESULT_SET_CACHE")
...See the Admin Guide for more on using the AdminAPI and AdminShell.
Non-XML document model results, BLOBs, and CLOBs cannot be cached.
The exact SQL string, including the cache hint if present, must match the cached entry for the results to be reused. This allows cache usage to skip parsing and resolving for faster responses.
Result set caching is not transactional. Transactions depend on (and enforce) consistency of data, and cached data is not guaranteed to be consistent with the data store’s data.
Clearing the result set cache clears all cache entries for all VDBs.