docs.intersystems.com
Home  /  Application Development: Core Topics  /  InterSystems SQL Optimization Guide  /  Cached Queries


InterSystems SQL Optimization Guide
Cached Queries
[Back]  [Next] 
InterSystems: The power behind what matters   
Search:  


The system automatically maintains a cache of prepared Dynamic 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 using Dynamic SQL, ODBC, JDBC, or the $SYSTEM.SQL.DDLImport() method. (The Management Portal execute SQL inteface, the InterSystems SQL Shell, and the %SYSTEM.SQL.Execute() method use Dynamic SQL, and thus create cached queries.) A non-cursor Embedded SQL statement does not create a cached query.
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 subsequent %Prepare() of the same query (differing only in specified literal values) uses the existing cached query rather than creating a new cached query. Note that changes to the query that shouldn’t affect query optimization, such as changing a column name alias or changing the ORDER BY clause, do result in different cached queries.
Changing the SetMapSelectability() 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 cache query is deleted when you purge cached queries. Modifying a table definition automatically purges any cached 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 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:
InterSystems 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 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
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 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() instance method or the %GetImplementationDetails() 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() 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:
  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:
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:
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.
Run Time Plan Choice
Run Time Plan Choice (RTPC) is a configuration option that allows the SQL optimizer to take advantage of outlier value information at run time (query execution time). Run Time Plan Choice is a system-wide SQL configuration option.
When RTPC is activated, preparing the query includes detecting whether the query contains a condition on a field that has an outlier value. If the prepare detects one or more outlier field conditions, the query is not sent to the optimizer. Instead, SQL generates a Run Time Plan Choice stub. At execution time, the optimizer uses this stub to choose which query plan to execute: a standard query plan that ignores outlier status, or an alternative query plan that optimizes for outlier status. If there are multiple outlier value conditions, the optimizer can choose from multiple alternative run time query plans.
Note that RTPC query plan display differs based on the source of the SQL code:
The Management Portal SQL interface Show Plan button may display an alternative run time query plan because this Show Plan takes its SQL code from the SQL interface text box.
The SQL Statement, when selected, displays the Statement Details which includes the Query Plan. This Query Plan does not display an alternative run time query plan, but instead contains the text execution may cause creation of a different plan” because it takes its SQL code from the statement index.
If RTPC is not activated, or the query does not contain appropriate outlier field conditions, or the query is a simple Embedded SQL query that does not use a cursor, the optimizer creates a standard SQL Statement and (for Dynamic SQL) a corresponding cached query.
If an RTPC stub is frozen, all associated alternative run time query plans are also frozen. RTPC processing remains active for a frozen query even when the RTPC configuration option is turned off.
You can manually suppress literal substitution when writing the query by specifying parentheses: SELECT Name,HaveContactInfo FROM t1 WHERE HaveContactInfo=(('Yes')). If you suppress literal substitution of the outlier field in a condition, RTPC is not applied to the query. The optimizer creates a standard cached query.
Activating RTPC
You can configure RTPC system-wide using either the Management Portal or a class method. Note that changing the RTPC configuration setting purges all cached queries.
You can activate RTPC using the Management Portal. From System Administration, select Configuration, then SQL and Object Settings, then General SQL Settings. On this screen you can set the Run Time Plan Choice check box option. If this option is not set (the default), the system does not perform Run Time Plan Choice.
You can activate RTPC using the $SYSTEM.SQL.SetRTPC(flag,.oldvalue) class method. The flag argument is a boolean used to set (1) or unset (0) RTPC. The oldvalue argument returns the prior RTPC setting as a boolean value.
Note:
Do not use the Bias queries as outlier configuration option. Leave this check box unselected.
Application of RTPC
The system applies RTPC to Dynamic SQL queries and Cursor-based Embedded SQL queries. It does not apply RTPC to single-row (non-cursor) Embedded SQL queries, or to INSERT, UPDATE, or DELETE statements.
The system applies RTPC to any field that Tune Table has determined to have an outlier value, when that field is specified in the following query contexts.
The outlier field is specified in a condition where it is compared to a literal. This comparison condition can be:
If RTPC is applied, the optimizer determines at run time whether to apply the standard query plan or an alternative query plan.
RTPC is not applied if the query contains unresolved ? input parameters.
RTPC is not applied if the query specifies the literal value surrounded by double parentheses, suppressing literal substitution.
RTPC is not applied if the literal is supplied to the outlier field condition by a subquery. However, RTPC is applied if there is an outlier field condition within a subquery.
Overriding RTPC
You can override RTPC for a specific query by specifying the %NORUNTIME restriction keyword. If the query SELECT Name,HaveContactInfo FROM t1 WHERE HaveContactInfo=? would result in RTPC processing, the query SELECT %NORUNTIME Name,HaveContactInfo FROM t1 WHERE HaveContactInfo=? would override RTPC, resulting in a standard query plan.
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:
  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 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:
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 InterSystems IRIS Management Portal. From System Administration, select Configuration, then SQL and Object Settings, then 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, InterSystems IRIS 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 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.USER.cls14..                Done
Export SQL Text for Cached Query: %sqlcq.USER.cls16..                Done
Export SQL Text for Cached Query: %sqlcq.USER.cls17..                Done
Export SQL Text for Cached Query: %sqlcq.USER.cls18..                Done
Export SQL Text for Cached Query: %sqlcq.USER.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.USER.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
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 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) 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:
Purging a cached query also purges related query performance statistics.
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. 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 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.USER.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. InterSystems SQL assigns this number to the next SQL statement you issue that performs a Prepare of a query.