Results Caching

The CData Virtuality Server provides the capability to cache the results of specific user queries and virtual procedure calls. This caching technique can yield significant performance gains if users of the system submit the same queries or execute the same procedures often.

Support Summary

Caching of user query results;
Caching of virtual procedure results;
Scoping of results is automatically determined to be VDB/user (replicated) or session level. The default logic will be influenced by every function evaluated, consider the DETERMINISM property on all source models/tables/procedures, and the Scope from the ExecutionContext or CacheDirective;
Configurable number of cache entries and time to live;
Administrative clearing.

User Interaction

User Query Cache

You can add a cache hint to the query. The most basic form of the cache hint, /*+ cache */, is sufficient to inform the engine that the results of the non-update command should be cached. Here is an example where the result set for PreparedStatement is cached:

CODE

...
PreparedStatement ps = connection.prepareStatement("/*+ cache */ select col from t where col2 = ?");
ps.setInt(1, 5);
ps.execute();
...

The results will be cached with the default time to live (TTL) and use the SQL string and the parameter value as part of the cache key.

The pref_mem and ttl options of the cache hint may also be used for result set cache queries. If a cache hint is not specified, then the default time to live of the result set caching configuration will be used. Here is how advanced result set caching looks:

CODE

/*+ cache(pref_mem ttl:60000) */ select col from t

In this example, the memory preference has been enabled and the time to live is set to 60,000 milliseconds or 1 minute. The TTL for an entry is actually treated as its maximum age and the entry may be purged sooner if the maximum number of cache entries has been reached.

Each query is re-checked for authorization using the current user’s permissions, regardless of whether or not the results have been cached.

Procedure Result Cache

Similar to cached views, cached virtual procedure results are used automatically when a matching set of parameter values is detected for the same procedure execution. Usage of the cached results may be bypassed when used with the OPTION NOCACHE clause (please refer to the subpage for more information).

Cached Virtual Procedure Definition

To indicate that a virtual procedure should be cached, its definition should include a cache hint. It is described in more detail on the dedicated subpage, and this is how it looks:

CODE

/*+ cache */

BEGIN

    ...

END

Results will be cached with the default ttl.

The pref_mem and ttl options of the cache hint may also be used for procedure caching.

Procedure results cache keys include the input parameter values. To prevent one procedure from filling the cache, at most 256 cache keys may be created per procedure per VDB.

A cached procedure will always produce all of its results prior to allowing those results to be consumed and placed in the cache. This differs from normal procedure execution which in some situations allows the returned results to be consumed in a streaming manner.

Cache Administration

The result set cache can be cleared using the SYSADMIN.clearResultSetCache() procedure.

Clearing the Result Set Cache

SQL

CALL SYSADMIN.clearResultSetCache();

Cache Configuration

By default, result set caching is enabled with 1,024 maximum entries with a maximum entry age of 2 hours. There are actually two caches configured with these settings. One cache holds results that are specific to sessions and is local to each CData Virtuality Server instance. The other cache holds VDB-scoped results and can be replicated. You can also override the default maximum entry age using 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 BufferManager and are subject to its configuration, including the restriction of maximum buffer space.

While the result data is not held in memory, cache keys, including parameter values, may be held in memory. Therefore, it is a good idea to limit the maximum size for the cache.

Limitations

Caching is permanently disabled for the user admin;
XML, BLOB, CLOB, and OBJECT types cannot be used as part of the cache key for a prepared statement of procedure cache keys;
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;
Clearing the results cache clears all cache entries for all VDBs.