Working with Cached Queries

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:

1. If the query matches a query already in the query cache. If not, it assigns an increment count to the query.

2. If the query prepares successfully. If not, it does not assign the increment count to a cached query name.

3. 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.

%sqlcq.namespace.hash

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.

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))).

Note:

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:

• Counting cached queries

• 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.

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.

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.