Query Result Caching
When a query is executed, the result is persisted (that is, cached). The result is purged from the system, if there is limitation to the memory allocated, or when the result is invalidated due to an update.
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, Ingres returns the same result that it returned previously. This can substantially reduce query time because Ingres bypasses query execution and instead retrieves the result directly from the cache.
Typically, query results are reused if all of 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.
• The query is not cancelled before returning all the results.
• The query is not distributed.
• The session is not connected to the master database iidbdb.
• There has not been ANY rollforwarddb.
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.
Note: If the query references standard Ingres tables (btree,hash,heap,isam) and the isolation level is repeatable read or serializable, query result cache is not read. This is because Ingres implements isolation via acquisition of locks, so isolation policies are violated by reading query result cache results.
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 OFF.
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 DBMS configuration parameter query_result_spill specifies if the in memory query results should spill to disk when the allocated query_result_memory runs low. Valid values are ON|OFF. The default is OFF.
When you have configured the installation with query_result_memory > 0 and query_result_spill ON, the dbms server starts an additional 'qrc-disk-spill' thread in the dbms server.
The spill thread will periodically wake up and check how much space is used in the query result cache, and optionally spill some least recently used results to disk. The spill file will be created in the II_WORK location.
The management database imadb is created when Ingres is installed and contains information about all activity that is current for a database server in the installation. This database can be used to monitor spill activity by doing the following select in imadb:
SELECT * from ima_qrc_stats;
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: 08/29/2024