Query Result Caching
When a query is executed, the result is persisted (that is, cached) for a period. At the end of the period, the result is purged from the system.
If a user repeats a query that has already been run, and the data in the tables has not changed since the last time the query was run, then the result of the query is the same. Instead of running the query again, Vector returns the same result that it returned previously. This can substantially reduce query time because Vector bypasses query execution and instead retrieves the result directly from the cache.
Typically, query results are reused if all the following conditions are met:
• The new query syntactically matches the previously executed query.
• The table or view data contributing to the query result has not changed.
• The persisted result for the previous query is still available.
• The role accessing the cached results has the required privileges.
• Any configuration options that affect how the result was produced have not changed.
• The query does not use a function that must be evaluated each time it is run.
• The query does not reference external tables.
• The query does not reference catalogs.
• The query does not reference gateway tables.
• The query does not select blob columns.
• The query does not reference global temporary table.
• The query is not a scrollable cursor.
• The query is not an update cursor.
Query result caching will improve performance only when there are multiple requests of the same select query; otherwise, the cost overhead incurred when using this feature may negate its benefits.
The DBMS configuration parameter query_result_cache in config.dat determines whether query result caching is ON, OFF, or used for either X100 or Ingres tables only. The result set can be referenced until it is invalidated. The default is ON.
The DBMS configuration parameter query_result_memory specifies the maximum amount of memory that can be allocated in the DBMS server for query result caching.
The SET query_result_cache statement can be used to override the system setting for query_result_cache if query_result_memory has also been configured.
Last modified date: 06/28/2024