Cached Queries

Cached Queries Improve Performance

When you first prepare a Dynamic SQL query, the SQL Engine optimizes it and generates a program (a set of one or more Caché routines) that will execute the query. The optimized query text is then stored as a cache 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 Caché.

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

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

Caché SQL stores all cached queries in a single location, the CACHE 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.

There is no need for a cache for Embedded SQL, because Embedded SQL statements are replaced with inline code at compilation time.

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 Caché 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

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

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 a Dynamic 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,63120) and DATENAME('MONTH',63120) 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

• Displaying cached queries using the Caché Management Portal

• Listing cached queries using the ^rINDEXSQL global

• Exporting cached queries to a file using the ExportSQL^%qarDDLExport utility

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 Using ^rINDEXSQL

You can use the ^rINDEXSQL global to list all of the cached queries and all of the SQL Statements for the current namespace:

  ZWRITE ^rINDEXSQL("sqlidx",2)

The third subscript is the location. For example, "%sqlcq.USER.cls4.1" is a cached query; "Sample.MyTable.1" is an SQL Statement.

The fourth subscript is the Statement hash.

Exporting Cached Queries to a File

The following utility lists all of the cached queries for the current namespace to a text file.

ExportSQL^%qarDDLExport(file,fileOpenParam,eos,cachedQueries,classQueries,classMethods,routines,display)

file	A file pathname where cached queries are to be listed. Specified as a quoted string. If the file does not exist, the system creates it. If the file already exists, Caché overwrites it.
fileOpenParam	Optional — The OPEN mode parameters for the file. Specified as a quoted string. The default is “WNS”. “W” specifies that the file is being opened for writing. “N” specifies that if the file does not exist, create a new sequential file with this name. “S” specifies stream format with carriage return, line feed, or form feed as default terminators. For a full list of OPEN mode parameters refer to the “Sequential File I/O” chapter of the Caché I/O Device Guide.
eos	Optional — The end-of-statement delimiter used to separate the individual cached queries in the listing. Specified as a quoted string. The default is “GO”.
cachedQueries	Optional — Export all SQL queries from the query cache to file. A boolean flag. The default is 1.
classQueries	Optional — Export all SQL queries from SQL class queries to file. A boolean flag. The default is 1.
classMethods	Optional — Export embedded SQL queries from class methods to file. A boolean flag. The default is 1.
routines	Optional — Export embedded SQL queries from MAC routines to file. This listing does not include system routines, cached queries, or generated routines. A boolean flag. The default is 1.
display	Optional — Display export progress on the Terminal screen. A boolean flag. The default is 0.

The following is an example of evoking this cached queries export utility:

  DO ExportSQL^%qarDDLExport("C:\temp\test\qcache.txt","WNS","GO",1,1,1,1,1)

When executed from the Terminal command line with display=1, export progress is displayed to the terminal screen, such as the following example:

Export SQL Text for Cached Query: %sqlcq.SAMPLES.cls14..                Done
Export SQL Text for Cached Query: %sqlcq.SAMPLES.cls16..                Done
Export SQL Text for Cached Query: %sqlcq.SAMPLES.cls17..                Done
Export SQL Text for Cached Query: %sqlcq.SAMPLES.cls18..                Done
Export SQL Text for Cached Query: %sqlcq.SAMPLES.cls19..                Done
Export SQL statement for Class Query: Cinema.Film.TopCategory...        Done
Export SQL statement for Class Query: Cinema.Film.TopFilms...           Done
Export SQL statement for Class Query: Cinema.FilmCategory.CategoryName...Done
Export SQL statement for Class Query: Cinema.Show.ShowTimes...          Done
Export SQL statement for Class Query: Cinema.TicketItem.ShowItem...     Done
Export SQL statement from Class Method: Aviation.EventCube.Fact.%BuildAllFacts...Done
Export SQL statement from Class Method: Aviation.EventCube.Fact.%BuildTempFile...Done
Export SQL statement from Class Method: Aviation.EventCube.Fact.%Count...Done
Export SQL statement from Class Method: Aviation.EventCube.Fact.%DeleteFact...Done
Export SQL statement from Class Method: Aviation.EventCube.Fact.%ProcessFact...Done
Export SQL statement from Class Method: Aviation.EventCube.Fact.%UpdateFacts...Done
Export SQL statement from Class Method: Aviation.EventCube.Star1032357136.%Count...Done
Export SQL statement from Class Method: Aviation.EventCube.Star1032357136.%GetDimensionProperty...Done
Export SQL statement from Class Method: Aviation.EventCube.Star1035531339.%Count...Done
Export SQL statement from Class Method: Aviation.EventCube.Star1035531339.%GetDimensionProperty...Done

20 SQL statements exported to script file C:\temp\test\qcache.txt

The created export file contains entries such as the following:

  -- SQL statement from Cached Query %sqlcq.SAMPLES.cls30
  SELECT TOP ? Name , Home_State , Age , AVG ( Age ) AS AvgAge FROM Sample . Person ORDER BY Home_State
GO

  -- SQL statement from Class Query Cinema.Film.TopCategory
#import Cinema
SELECT TOP 3 ID, Description, Length, Rating, Title, Category->CategoryName
  FROM Film
  WHERE (PlayingNow = 1) AND (Category = :P1)
  ORDER BY TicketsSold DESC
GO

  -- SQL statement(s) from Class Method Aviation.EventCube.Fact.%Count
#import Aviation.EventCube
SELECT COUNT(*) INTO :tCount FROM Aviation_EventCube.Fact
GO

This cached queries listing can be used as input to the Query Optimization Plans utility.

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 Terminal: You can directly execute a cached query using the ExecuteCachedQuery()Opens in a new tab method of the $SYSTEM.SQL class. This method allows you to specify input parameter values and to limit the number of rows to output. You can execute a Dynamic SQL %SQL.StatementOpens in a new tab cached query or an xDBC cached query from the Terminal command line. This method is primarily useful for testing an existing cached query on a limited subset of the data.

• From the Management Portal SQL Interface: Follow the “Displaying 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 SetCachedQueryLockTimeout()Opens in a new tab method, which 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.

The SetCachedQueryLockTimeout() method sets the timeout value system-wide and returns the previous value:

SetCQTimeout
   DO $SYSTEM.SQL.SetCachedQueryLockTimeout(150,.oldval)
   WRITE oldval," prior value cached query seconds",!!
SetCQTimeoutAgain
   DO $SYSTEM.SQL.SetCachedQueryLockTimeout(180,.oldval2)
   WRITE oldval2," prior value cached query seconds",!!
ResetCQTimeoutToDefault
   DO $SYSTEM.SQL.SetCachedQueryLockTimeout(,.oldval3)
   WRITE oldval3," prior value cached query seconds"

SetCachedQueryLockTimeout() 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 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, 2014, it will purge only the cached queries that were last prepared before April 11, 2014. 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. 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.

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

Purging a cached query also purges related query performance statistics (SQLStats).

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.