Working with Cached Queries
The system automatically maintains a cache of prepared SQL statements (“queries”). This permits the re-execution of an SQL query without repeating the overhead of optimizing the query and developing a Query Plan. A cached query is created when certain SQL statements are prepared. Preparing a query occurs at runtime, not when the routine containing the SQL query code is compiled. Commonly, a prepare is immediately followed by the first execution of the SQL statement, though in Dynamic SQL it is possible to prepare a query without executing it. Subsequent executions ignore the prepare statement and instead access the cached query. To force a new prepare of an existing query it is necessary to purge the cached query.
All invocations of SQL create cached queries, whether invoked in an ObjectScript routine or a class method.
-
Dynamic SQL, ODBC, JDBC, and the $SYSTEM.SQL.DDLImport() method create a cached query when the query is prepared. The Management Portal execute SQL interface, the InterSystems SQL Shell, and the %SYSTEM.SQL.Execute()Opens in a new tab method use Dynamic SQL, and thus use a prepare operation to create cached queries.
They are listed in the Management Portal general Cached Queries listing for the namespace (or specified schema), the Management Portal Catalog Details Cached Queries listings for each table being accessed, and the SQL Statements listings. Dynamic SQL follows the cached query naming conventions described in this chapter.
-
Class Queries create a cached query upon prepare (%PrepareClassQuery() method) or first execution (CALL).
They are listed in the Management Portal general Cached Queries listing for the namespace. If the class query is defined in a Persistent class, the cached query is also listed in the Catalog Details Cached Queries for that class. It is not listed in the Catalog Details for the table(s) being accessed. It is not listed in the SQL Statements listings. Class queries follow the cached query naming conventions described in this chapter.
-
Embedded SQL creates a cached query upon first execution of the SQL code, or the initiation of code execution by invoking the OPEN command for a declared cursor. Embedded SQL cached queries are listed in the Management Portal Cached Queries listings with a Query Type of Embedded cached SQL, and the SQL Statements listings. Embedded SQL cached queries follow a different cached query naming convention.
Cached queries of all types are deleted by all purge cached queries operations.
SQL query statements that generate a cached query are:
-
SELECT: a SELECT cached query is shown in the Catalog Details for its table. If the query references more than one table, the same cached query is listed for each referenced table. Purging the cached query from any one of these tables purges it from all tables. From the table’s Catalog Details you can select a cached query name to display cached query details, including Execute and Show Plan options. A SELECT cached query created by the $SYSTEM.SQL.Schema.ImportDDL("IRIS")Opens in a new tab method does not provide Execute and Show Plan options.
DECLARE name CURSOR FOR SELECT creates a cached query. However, cached query details do not include Execute and Show Plan options.
-
CALL: creates a cached query shown in the Cached Queries list for its schema.
-
INSERT, UPDATE, INSERT OR UPDATE, DELETE: create a cached query shown in the Catalog Details for its table.
-
TRUNCATE TABLE: creates a cached query shown in the Catalog Details for its table. Note that $SYSTEM.SQL.Schema.ImportDDL("IRIS")Opens in a new tab does not support TRUNCATE TABLE.
-
SET TRANSACTION, START TRANSACTION, %INTRANSACTION, COMMIT, ROLLBACK: create a cached query shown in the Cached Queries list for every schema in the namespace.
A cached query is created when you Prepare the query. For this reason, it is important not to put a %Prepare() method in a loop structure. A subsequent %Prepare() of the same query (differing only in specified literal values) uses the existing cached query rather than creating a new cached query.
Changing the SetMapSelectability()Opens in a new tab value for a table invalidates all existing cached queries that reference that table. A subsequent Prepare of an existing query creates a new cached query and removes the old cached query from the listing.
A cached query is deleted when you purge cached queries. Modifying a table definition automatically purges any queries that reference that table. Issuing a Prepare or Purge automatically requests an exclusive system-wide lock while the query cache metadata is updated. The System Administrator can modify the timeout value for the cached query lock.
The creation of a cached query is not part of a transaction. The creation of a cached query is not journaled.
Cached Queries Improve Performance
When you first prepare a query, the SQL Engine optimizes it and generates a program (a set of one or more InterSystems IRIS® data platform routines) that will execute the query. The optimized query text is then stored as a cached query class. If you subsequently attempt to execute the same (or a similar) query, the SQL Engine will find the cached query and directly execute the code for the query, bypassing the need to optimize and code generate.
Cached queries provide the following benefits:
-
Subsequent execution of frequently used queries is faster. More importantly, this performance boost is available automatically without having to code cumbersome stored procedures. Most relational database products recommend using only stored procedures for database access. This is not necessary with InterSystems IRIS.
-
A single cached query is used for similar queries, queries that differ only in their literal values. For example, SELECT TOP 5 Name FROM Sample.Person WHERE Name %STARTSWITH 'A' and SELECT TOP 1000 Name FROM Sample.Person WHERE Name %STARTSWITH 'Mc' only differ in the literal values for TOP and the %STARTSWITH condition. The cached query prepared for the first query is automatically used for the second query. For other considerations that result in two “identical” queries resulting in separate cached queries, see below.
-
The query cache is shared among all database users; if User 1 prepares a query, then User 1023 can take advantage of it.
-
The Query Optimizer is free to use more time to find the best solution for a given query as this price only has to be paid the first time a query is prepared.
InterSystems SQL stores all cached queries in a single location, the IRISLOCALDATA database. However, cached queries are namespace specific. Each cached query is identified with the namespace from which it was prepared (generated). You can only view or execute a cached query from within the namespace in which it was prepared. You can purge cached queries either for the current namespace or for all namespaces.
A cached query does not include comments. However, it can include comment options following the query text, such as /*#OPTIONS {"optionName":value} */.
Since a cached query uses an existing query plan, it provides continuity of operation for existing queries. Changes to the underlying tables such as adding indexes or redefining the table optimization statistics have no effect on an existing cached query if the plan is frozen. For use of cached queries when changing a table definition, refer to the “SQL Statements and Frozen Plans” chapter in this manual.
Creating a Cached Query
When InterSystems IRIS Prepares a query it determines:
-
If the query matches a query already in the query cache. If not, it assigns an increment count to the query.
-
If the query prepares successfully. If not, it does not assign the increment count to a cached query name.
-
Otherwise, the increment count is assigned to a cached query name and the query is cached.
Cached Query Names for Dynamic SQL
The SQL Engine assigns a unique class name to each cached query, with the following format:
%sqlcq.namespace.clsnnn
Where namespace is the current namespace, in capital letters, and nnn is a sequential integer. For example, %sqlcq.USER.cls16.
Cached queries are numbered sequentially on a per-namespace basis, starting with 1. The next available nnn sequential number depends on what numbers have been reserved or released:
-
A number is reserved when you begin to prepare a query if that query does not match an existing cached query. A query matches an existing cached query if they differ only in their literal values — subject to certain additional considerations: suppressed literal substitution, different comment options, or the situations described in “Separate Cached Queries”.
-
A number is reserved but not assigned if the query does not prepare successfully. Only queries that Prepare successfully are cached.
-
A number is reserved and assigned to a cached query if the query prepares successfully. This cached query is listed for every table referred to in the query, regardless of whether any data is accessed from that table. If a query does not refer to any tables, a cached query is created but cannot be listed or purged by table.
-
A number is released when a cached query is purged. This number becomes available as the next nnn sequential number. Purging individual cached queries associated with a table or purging all of the cached queries for a table releases the numbers assigned to those cached queries. Purging all cached queries in the namespace releases all of the numbers assigned to cached queries, including cached queries that do not reference a table, and numbers reserved but not assigned.
Purging cached queries resets the nnn integer. Integers are reused, but remaining cached queries are not renumbered. For example, a partial purge of cached queries might leave cls1, cls3, cls4, and cls7. Subsequent cached queries would be numbered cls2, cls5, cls6, and cls8.
A CALL statement may result in multiple cached queries. For example, the SQL statement CALL Sample.PersonSets('A','MA') results in the following cached queries:
%sqlcq.USER.cls1: CALL Sample . PersonSets ( ? , ? )
%sqlcq.USER.cls2: SELECT name , dob , spouse FROM sample . person
WHERE name %STARTSWITH ? ORDER BY 1
%sqlcq.USER.cls3: SELECT name , age , home_city , home_state
FROM sample . person WHERE home_state = ? ORDER BY 4 , 1
In Dynamic SQL, after preparing an SQL query (using the %Prepare() or %PrepareClassQuery() instance method) you can return the cached query name using the %Display()Opens in a new tab instance method or the %GetImplementationDetails()Opens in a new tab instance method. See Results of a Successful Prepare.
The cached query name is also a component of the result set OREF returned by the %Execute()Opens in a new tab instance method of the %SQL.StatementOpens in a new tab class (and the %CurrentResultOpens in a new tab property). Both of these methods of determining the cached query name are shown in the following example:
SET randtop=$RANDOM(10)+1
SET randage=$RANDOM(40)+1
SET myquery = "SELECT TOP ? Name,Age FROM Sample.Person WHERE Age < ?"
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET x = tStatement.%GetImplementationDetails(.class,.text,.args)
IF x=1 { WRITE "cached query name is: ",class,! }
SET rset = tStatement.%Execute(randtop,randage)
WRITE "result set OREF: ",rset.%CurrentResult,!
DO rset.%Display()
WRITE !,"A sample of ",randtop," rows, with age < ",randage
In this example, the number of rows selected (TOP clause) and the WHERE clause predicate value change with each query invocation, but the cached query name does not change.
Cached Query Names for Embedded SQL
The SQL Engine assigns a unique class name to each Embedded SQL cached query, with the following format:
%sqlcq.namespace.hash
Where namespace is the current namespace, in capital letters, and hash is a unique hash value. For example, %sqlcq.USER.xEM1h5QIeF4l3jhLZrXlnThVJZDh.
Embedded SQL cached queries are listed in the Management Portal for each table in the Catalog Details Cached Queries listing for each table with this Class Name and a Query Type of Embedded cached SQL.
Separate Cached Queries
Differences between two queries that shouldn’t affect query optimization nevertheless generate separate cached queries:
-
Different syntactic forms of the same function generate separate cached queries. Thus ASCII('x') and {fn ASCII('x')} generate separate cached queries, and {fn CURDATE()} and {fn CURDATE} generate separate cached queries.
-
A case-sensitive table alias or column alias value, and the presence or absence of the optional AS keyword generate separate cached queries. Thus ASCII('x'), ASCII('x') AChar, and ASCII('x') AS AChar generate separate cached queries.
-
Using a different ORDER BY clause.
-
Using TOP ALL instead of TOP with an integer value.
Literal Substitution
When the SQL Engine caches an SQL query, it performs literal substitution. The query in the query cache represents each literal with a “?” character, representing an input parameter. This means that queries that differ only in their literal values are represented by a single cached query. For example, the two queries:
SELECT TOP 11 Name FROM Sample.Person WHERE Name %STARTSWITH 'A'
SELECT TOP 5 Name FROM Sample.Person WHERE Name %STARTSWITH 'Mc'
Are both represented by a single cached query:
SELECT TOP ? Name FROM Sample.Person WHERE Name %STARTSWITH ?
This minimizes the size of the query cache, and means that query optimization does not need to be performed on queries that differ only in their literal values.
Literal values supplied using input host variables (for example, :myvar) and ? input parameters are also represented in the corresponding cached query with a “?” character. Therefore, the queries SELECT Name FROM t1 WHERE Name='Adam', SELECT Name FROM t1 WHERE Name=?, and SELECT Name FROM t1 WHERE Name=:namevar are all matching queries and generate a single cached query.
You can use the %GetImplementationDetails() method to determine which of these entities is represented by each “?” character for a specific prepare.
The following considerations apply to literal substitution:
-
Plus and minus signs specified as part of a literal generate separate cached queries. Thus ABS(7), ABS(-7), and ABS(+7) each generate a separate cached query. Multiple signs also generate separate cached queries: ABS(+?) and ABS(++?). For this reason, it is preferable to use an unsigned variable ABS(?) or ABS(:num), for which signed or unsigned numbers can be supplied without generating a separate cached query.
-
Precision and scale values usually do not take literal substitution. Thus ROUND(567.89,2) is cached as ROUND(?,2). However, the optional precision value in CURRENT_TIME(n), CURRENT_TIMESTAMP(n), GETDATE(n), and GETUTCDATE(n) does take literal substitution.
-
A boolean flag does not take literal substitution. Thus ROUND(567.89,2,0) is cached as ROUND(?,2,0) and ROUND(567.89,2,1) is cached as ROUND(?,2,1).
-
A literal used in an IS NULL or IS NOT NULL condition does not take literal substitution.
-
Any literal used in an ORDER BY clause does not take literal substitution. This is because ORDER BY can use an integer to specify a column position. Changing this integer would result in a fundamentally different query.
-
An alphabetic literal must be enclosed in single quotes. Some functions permit you to specify an alphabetic format code with or without quotes; only a quoted alphabetic format code takes literal substitution. Thus DATENAME(MONTH,64701) and DATENAME('MONTH',64701) are functionally identical, but the corresponding cached queries are DATENAME(MONTH,?) and DATENAME(?,?).
-
Functions that take a variable number of arguments generate separate cached queries for each argument count. Thus COALESCE(1,2) and COALESCE(1,2,3) generate separate cached queries.
Literal Substitution and Performance
The SQL Engine performs literal substitution for each value of an IN predicate. A large number of IN predicate values can have a negative effect on cached query performance. A variable number of IN predicate values can result in multiple cached queries. Converting an IN predicate to an %INLIST predicate results in a predicate with only one literal substitution, regardless of the number of listed values. %INLIST also provides an order-of-magnitude SIZE argument, which SQL uses to optimize performance.
Suppressing Literal Substitution
This literal substitution can be suppressed. There are circumstances where you may wish to optimize on a literal value, and create a separate cached query for queries with that literal value. To suppress literal substitution, enclose the literal value in double parentheses. This is shown in the following example:
SELECT TOP 11 Name FROM Sample.Person WHERE Name %STARTSWITH (('A'))
Specifying a different %STARTSWITH value would generate a separate cached query. Note that suppression of literal substitution is specified separately for each literal. In the above example, specifying a different TOP value would not generate a separate cached query.
To suppress literal substitution of a signed number, specify syntax such as ABS(-((7))).
Different numbers of enclosing parentheses may also suppress literal substitution in some circumstances. InterSystems recommends always using double parentheses as the clearest and most consistent syntax for this purpose.
Cached Query Result Set
When you execute a cached query it creates a result set. A cached query result set is an Object instance. This means that the values you specify for literal substitution input parameters are stored as object properties. These object properties are referred to using i%PropName syntax.
Listing Cached Queries
You can count and list existing cached queries in the current namespace:
-
Listing cached queries using the InterSystems IRIS Management Portal
-
Listing tables referenced by a cached query or a stored procedure using the InterSystems IRIS Management Portal
Counting Cached Queries
You can determine the current number of cached queries for a table by invoking the GetCachedQueryTableCount()Opens in a new tab method of the %Library.SQLCatalogOpens in a new tab class. This is shown in the following example:
SET tbl="Sample.Person"
SET num=##class(%Library.SQLCatalog).GetCachedQueryTableCount(tbl)
IF num=0 {WRITE "There are no cached queries for ",tbl }
ELSE {WRITE tbl," is associated with ",num," cached queries" }
Note that a query that references more than one table creates a single cached query. However, each of these tables counts this cached query separately. Therefore, the number of cached queries counted by table may be larger than the number of actual cached queries.
Listing Cached Queries
You can list (and manage) the contents of the query cache using the InterSystems IRIS Management Portal. From System Explorer, select SQL. Select a namespace with the Switch option at the top of the page; this displays the list of available namespaces. On the left side of the screen open the Cached Queries folder. Selecting one of these cached queries displays the details.
The Query Type can be one of the following values:
-
%SQL.Statement Dynamic SQL: a Dynamic SQL query using %SQL.Statement.
-
Embedded cached SQL: an Embedded SQL query.
-
ODBC/JDBC Statement: a dynamic query from either ODBC or JDBC.
The Statement is displayed for a Dynamic SQL cached query. This consists of the statement hash, which is a selectable link that takes you to the SQL Statement Details, and the plan state, such as (Unfrozen) or (Frozen/Explicit).
When you successfully prepare an SQL statement, the system generates a new class that implements the statement. If you have set the Retain cached query source system-wide configuration option, the source code for this generated class is retained and can be opened for inspection using Studio. To do this, go to the InterSystems IRIS Management Portal. From System Administration, select Configuration, then SQL and Object Settings, then SQL. On this screen you can set the Retain cached query source option. If this option is not set (the default), the system generates and deploys the class and does not save the source code.
You can also set this system-wide option using the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method, as follows: SET status=$SYSTEM.SQL.Util.SetOption("CachedQuerySaveSource",flag,.oldval). The flag argument is a boolean used to retain (1) or not retain (0) query source code after a cached query is compiled; the default is 0. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab.
Listing Tables Referenced by a Cached Query
A cached query can reference multiple tables, views, and procedures.
-
For tables referenced by a given cached query or stored procedure, use Management Portal SQL Statements tab to list SQL Statements. You can use the Filter option provided with this tab to filter by a Location(s) column value:
-
Cached Query: For example, the Location(s) column value %sqlcq.USER.cls2.1 lists all the tables referenced by that cached query in the Table/View/Procedure Name(s) column.
-
Stored Procedure: For example, the Location(s) column value Sample.procNamesJoinSP.1 lists all the tables referenced by that stored procedure in the Table/View/Procedure Name(s) column.
-
-
For cached queries for a given table, use the Management Portal display of cached queries.
Executing Cached Queries
-
From Dynamic SQL: A %SQL.StatementOpens in a new tab Prepare operation (%Prepare(), %PrepareClassQuery(), or %ExecDirect()) creates a cached query. A Dynamic SQL %Execute() method using the same instance executes the most recently prepared cached query.
-
From the Management Portal SQL Interface: Follow the “Listing Cached Queries” instructions above. From the selected cached query’s Catalog Details tab, click the Execute link.
Cached Query Lock
Issuing a Prepare or Purge statement automatically requests an exclusive system-wide lock while the cached query metadata is updated. SQL supports the system-wide CachedQueryLockTimeout option of the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method. This option governs lock timeout when attempting to acquire a lock on cached query metadata. The default is 120 seconds. This is significantly longer than the standard SQL lock timeout, which defaults to 10 seconds. A System Administrator may need to modify this cached query lock timeout on systems with large numbers of concurrent Prepare and Purge operations, especially on a system which performs bulk purges involving a large number (several thousand) cached queries.
SET status=$SYSTEM.SQL.Util.SetOption("CachedQueryLockTimeout",seconds,.oldval) method sets the timeout value system-wide:
SetCQTimeout
SET status=$SYSTEM.SQL.Util.SetOption("CachedQueryLockTimeout",150,.oldval)
WRITE oldval," initial value cached query seconds",!!
SetCQTimeoutAgain
SET status=$SYSTEM.SQL.Util.SetOption("CachedQueryLockTimeout",180,.oldval2)
WRITE oldval2," prior value cached query seconds",!!
ResetCQTimeoutToDefault
SET status=$SYSTEM.SQL.Util.SetOption("CachedQueryLockTimeout",oldval,.oldval3)
CachedQueryLockTimeout sets the cached query lock timeout for all new processes system-wide. It does not change the cached query lock timeout for existing processes.
Purging Cached Queries
Whenever you modify (alter or delete) a table definition, any queries based on that table are automatically purged from the query cache on the local system. If you recompile a persistent class, any queries that use that class are automatically purged from the query cache on the local system.
You can explicitly purge cached queries via the Management Portal using one of the Purge Cached Queries options. You can explicitly purge cached queries using the SQL command PURGE CACHED QUERIES. You can explicitly purge cached queries using the SQL Shell PURGE command.
You can use the $SYSTEM.SQL.Purge(n)Opens in a new tab method to explicitly purge cached queries that have not been recently used. Specifying n number of days purges all cached queries in the current namespace that have not been used (prepared) within the last n days. Specifying an n value of 0 or "" purges all cached queries in the current namespace. For example, if you issue a $SYSTEM.SQL.Purge(30) method on May 11, 2018, it will purge only the cached queries that were last prepared before April 11, 2018. A cached query that was last prepared exactly 30 days ago (April 11, in this example) would not be purged.
You can also purge cached queries using the following methods:
-
$SYSTEM.SQL.PurgeCQClass()Opens in a new tab purges one or more cached queries by name in the current namespace. You can specify cached query names as a comma-separated list. Cached query names are case sensitive; the namespace name must be specified in all-capital letters. The specified cached query name or list of cached query names must be enclosed with quotation marks.
-
$SYSTEM.SQL.PurgeForTable()Opens in a new tab purges all cached queries in the current namespace that reference the specified table. The schema and table name are not case-sensitive.
-
$SYSTEM.SQL.PurgeAllNamespaces()Opens in a new tab purges all cached queries in all namespaces on the current system. Note that when you delete a namespace, its associated cached queries are not purged. Executing PurgeAllNamespaces() checks if there are any cached queries associated with namespaces that no longer exist; if so, these cached queries are purged.
To purge all cached queries in the current namespace, use the Management Portal Purge ALL queries for this namespace option.
Purging a cached query also purges related query performance statistics.
Purging a cached query also purges related SQL Statement list entries. SQL Statements listed in the Management Portal may not be immediately purged, you may have to press the Clean stale button to purge these entries from the SQL Statements list.
When you change the system-wide default schema name, the system automatically purges all cached queries in all namespaces on the system.
Remote Systems
Purging a cached query on a local system does not purge copies of that cached query on mirror systems. Copies of a purged cached query on a remote system must be manually purged.
When a persistent class is modified and recompiled, the local cached queries based on that class are automatically purged. InterSystems IRIS does not automatically purge copies of those cached queries on remote systems. This could mean that some cached queries on a remote system are “stale” (no longer valid). However, when a remote system attempts to use a cached query, the remote system checks whether any of the persistent classes that the query references have been recompiled. If a persistent class on the local system has been recompiled, the remote system automatically purges and recreates the stale cached query before attempting to use it.
SQL Commands That Are Not Cached
The following non-query SQL commands are not cached; they are purged immediately after use:
-
Data Definition Language (DDL): CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE VIEW, ALTER VIEW, DROP VIEW, CREATE INDEX, DROP INDEX, CREATE FUNCTION, CREATE METHOD, CREATE PROCEDURE, CREATE QUERY, DROP FUNCTION, DROP METHOD, DROP PROCEDURE, DROP QUERY, CREATE TRIGGER, DROP TRIGGER, CREATE DATABASE, USE DATABASE, DROP DATABASE
-
User, Role, and Privilege: CREATE USER, ALTER USER, DROP USER, CREATE ROLE, DROP ROLE, GRANT, REVOKE, %CHECKPRIV
-
Locking: LOCK TABLE, UNLOCK TABLE
-
Miscellaneous: SAVEPOINT, SET OPTION
Note that if you issue one of these SQL commands from the Management Portal Execute Query interface, the Performance information includes text such as the following: Cached Query: %sqlcq.USER.cls16. This appears in indicate that a cached query name was assigned. However, this cached query name is not a link. No cached query was created, and the incremental cached query number .cls16 was not set aside. InterSystems SQL assigns this cached query number to the next SQL command issued.