Result Set Caching
The allows query result sets to be cached on the server and returned quickly on subsequent queries. Result set caching is managed by the service class under which the query is executed. As with other service class attributes, you can override this by using keywords on the query itself. Result set caching uses the normalized text of the query (i.e. with comments removed, whitespace reduced, case normalized) to determine whether two queries are the same and thus can potentially use the cached result of a previous query.
Result set caching is affected by two service class attributes.
cache_max_bytes | Controls whether the database stores the results of any specified query in the cache. All queries executed using a service class specifying this attribute will have their results stored in the cache if the size of the result is smaller than this value. You can determine the size of a result set in bytes by querying the bytes_returned field of the completed_queries virtual table. The default value is 0, and thus by default, the database does not cache any results. The database caches results in memory on the SQL Nodes. These results are not cached if there is insufficient memory available. |
---|---|
cache_max_time | Affects whether a specified query uses results from the cache rather than executing the query. If there is a cached result with the same query text, executed less than cache_max_time seconds ago, the database uses that cached result. The default value is 0, and thus by default, the database does not return any cached results. The cache considers all SQL Nodes, and if a potential cached result is only available on a different SQL Node, the database redirects the query to that node. If you specify the force attribute on the connection, which disables load balancing and redirection, the database considers only cached results on the current node. |
When a given query uses multiple service classes, the most restrictive values are used. For example if a query uses service_class_a with cache_max_bytes set to 512, and service_class_b with cache_max_bytes set to 0, the results will not be cached regardless of the size of the result set because the most restrictive value is 0.
Similarly, the values for cache_max_bytes and cache_max_time can be overriden at query time by specifying them on the query itself, for example:
This indicates that the results of this query will be cached if the result is less than or equal to 32 bytes, and that the cache will be used if the value stored in the cache is less than 60 seconds old. It is very important to note that attributes specified on the query cannot increase the values from the service classes, only reduce them.
Both cache_max_bytes and cache_max_time can be explicitly not specified in a service class by using the value -1. -1 effectively is treated as 0 if not overriden. unspecified, or -1 values can always be overriden by other service classes or on the query. A typical use case is to:
- specify cache_max_bytes as 0 for service classes used by ad hoc queries, where results should never be cached and consume memory.
- specify cache_max_bytes and cache_max_time as -1 on service classes used by repeated production queries. As mentioned, these will be treated as 0 if not overridden.
- explicitly override cache_max_bytes on repeated production queries whose results should be cached.
- explicitly override cache_max_time on repeated production queries whose results can be retrieved from the cache.
Note that because the default schema used for a query can affect the results of the query, two queries with identical text but making use of different schemas will be treated differently.
Finally, the contents of the cache can be examined using the result_cache virtual table.
Queries are cancelled when they exceed the max_elapsed_time service class value. An additional service class value, however, allows queries to continue beyond max_elapsed_time so that their results can be stored in the cache. The service class (or SQL) can specify max_elapsed_time_for_caching, which should be a value larger than max_elapsed_time, and that indicates the total time the query can continue executing such that its results will be stored in the cache.
max_elapsed_time_for_caching and max_elapsed_time are both measured in seconds. When a query with max_elapsed_time_for_caching exceeds max_elapsed_time, a warning indicating Query exceeded time limit, but will continue to run and be cached (Limited to 200 second(s) for caching from query) will be returned to the JDBC driver, where max_elapsed_time_for_caching was set to 200 seconds in this example.
max_elapsed_time_for_caching is only supported on queries executed using the JDBC driver.
You can store and retrieve query results from the cache depending on the settings of the cache_max_bytes and cache_max_time values either using the SQL statement or the service class specified for the query execution. Then, you can invalidate the result set cache using these DDL statements. Use these statements when you perform table updates such as an INSERT INTO SQL statement operation or the execution of one of the loading and transformation functions.
Syntax
Remove the cached result sets for all queries that reference the specified table.
You can invalidate the cached result sets for only tables where you have the INSERT privilege. If a cached query references multiple tables, you only need the INSERT privilege on any one of those tables.
Parameter | Data Type | Description |
---|---|---|
table_name | string | Name of the database table. |
seconds | numeric | Number of seconds that specifies the interval at which to invalidate a section of the result set cache. |
Examples
This example immediately invalidates the cache and removes the cached result sets for all queries that reference the employees table.
This example invalidates the cache entries for queries that reference the employees table for a fraction of the cache every second. Specify the fraction to be 1/10 by using the spread_over_time parameter. The database removes 1/10 of the cached result set immediately, then the next 1/10 of the cached result set the subsequent second, and so on. Invalidation causes the re-execution of queries to delay. Spreading the invalidation over time prevents spikes of expensive query executions.
Syntax
Remove the cached result sets in the specified database.
Parameter | Data Type | Description |
---|---|---|
database_name | string | Name of the database. |
seconds | numeric | Number of seconds that specifies the interval at which to invalidate a section of the result set cache. |
Examples This example immediately invalidates the cache and removes the cached result sets in the test_database database.
This example invalidates the cache entries in the test_database database for a fraction of the cache every second. Specify the fraction to be 1/10 by using the spread_over_time parameter. The database removes 1/10 of the cached result set immediately, then the next 1/10 of the cached result set the subsequent second, and so on. Invalidation causes the re-execution of queries to delay. Spreading the invalidation over time prevents spikes of expensive query executions.
Query Ocient