Caché SQL Optimization Guide
Cached Queries
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

The system automatically maintains a cache of recent Dynamic SQL statements (“queries”). This permit re-execution of the SQL statement without repeating the overhead of preparing the SQL statement. A cached query is created when certain SQL statements are prepared using Dynamic SQL, ODBC, JDBC, or the $SYSTEM.SQL.DDLImport() method. (The Management Portal execute SQL inteface and the Caché SQL Shell use Dynamic SQL, and thus create cached queries.) Embedded SQL does not create cached queries.

SQL statements that are automatically cached are:
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 cache 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 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:
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.
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.SAMPLES.cls16.
The numbering of cached query names is as follows:
In Dynamic SQL, after preparing an SQL query (using the %Prepare() instance method) you can return the cached query name using the %GetImplementationDetails() instance method. The cached query name is also component of the result set oref returned by the %Execute() instance method of the %SQL.Statement class (and the %CurrentResult property). Both of these methods of determining the cached query name are shown in the following example:
  ZNSPACE "SAMPLES"
  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.
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.
Input host variables (for example, :myvar) and ? input parameters in the query text are also represented in the corresponding cached query with a “?” character. You can use the %GetImplementationDetails() method to determine which of these entities is represented by each “?” character in the cached query.
The following considerations apply to literal substitution:
The following functions do not perform literal substitution: CHAR_LENGTH, CHARACTER_LENGTH, CURRENT_TIMESTAMP.
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))).
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.
Existing Cached Queries
You can count and view existing cached queries. There are two ways to view the cached queries for the current namespace:
Counting Cached Queries
You can determine the current number of cached queries for a table by invoking the GetCachedQueryTableCount() method of the %Library.SQLCatalog class. This is shown in the following example:
  ZNSPACE "Samples"
  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" }
 
Displaying Cached Queries
You can view (and manage) the contents of the query cache using the Caché Management Portal. From System Explorer, select SQL ([Home] > [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:
When you successfully prepare an SQL statement, the system generates a new class that implements the statement. If you have set the Cached Query - Save Source 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 Caché Management Portal. From System Administration, select Configuration, then SQL and Object Settings, then General SQL Settings ([Home] > [Configuration] > [General SQL Settings]). On this screen you can set the Cached Query - Save Source option. If this option is not set (the default), the system generates and deploys the class and does not save the source code.
Listing 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
A Dynamic SQL %Execute() method executes a query from the query cache.
You can directly execute an ODBC or JDBC cached query created at a client from the server command line, using the ExecuteCachedQuery() method of the $SYSTEM.SQL class. This method allows you to specify input parameter values and to limit the number of rows to output. It is primarily useful for debugging an existing xDBC cached query on a limited subset of the data.
You can execute a cache query from the Management Portal. 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() 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 a value 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"
 
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) 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:
Purging a cached query also purges related query performance statistics (SQLStats).
Caution:
When you change the systemwide 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 shadow or 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. Caché 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 Statements That Are Not Cached
The following non-query SQL statements are not cached; they are purged immediately after use:
Note that if you issue one of these SQL statements from the Management Portal Execute Query interface, the Performance information includes text such as the following: Class: %sqlcq.SAMPLES.cls16. This appears in indicate that a cached query name was assigned. However, no cached query was created, and the incremental cached query number .cls16 was not set aside. Caché SQL assigns this number to the next SQL statement you issue that performs a Prepare of a query.