Chapter 4 Cache Query (1)
The system automatically maintains the cache of prepared SQL statements (” queries “). This allows the SQL query to be re-executed without the overhead of repeatedly optimizing the query and developing the query plan. Cache queries are created when some SQL statements are prepared. Prepare the query at run time, not when you compile the routine that contains the SQL query code. Typically, PREPARE follows the first execution of an SQL statement, but in dynamic SQL, you can PREPARE the query without executing it. Subsequent executions ignore the PREPARE statement and access the cached query instead. To force new preparations for existing queries, cached queries must be cleared.
All SQL calls create cached queries, whether called in an ObjectScript routine or a class method.
- Dynamic SQL, ODBC, JDBC, and
$SYSTEM.SQL.DDLImport()Method creates a cached query when the query is prepared. The management portal executes SQL interfaces, InterSystems SQL Shell, and%SYSTEM.SQL.Execute()Method uses dynamic SQL, so a prepare action is used to create cached queries.
They are listed in the Management Portal general cache query list for the namespace (or a given schema), the Management Portal Catalog Details cache query list for each table being accessed, and the LIST of SQL statements. Dynamic SQL follows the caching query naming convention described in this chapter.
- Class query in preparation (
%PrepareClassQuery()Method) or the first execution (call) to create a cache query.
They are listed in the namespace’s management portal general cache query list. If the class query is defined in a persistent class, the cached query is also listed in the Catalog Details cache query for that class. It is not listed in the directory details of the table being accessed. It is not listed in the SQL statement list. Class queries follow the caching query naming convention described in this chapter.
- Embedded SQL first executes SQL code or declares cursors through calls
OPENCommand to initiate code execution to create cache queries. Embedded SQL cache query columns are displayed in the MANAGEMENT portal cache query list. The query type is embedded CACHED SQL, which is a list of SQL statements. Embedded SQL cache queries follow different cache query naming conventions.
All clear cache queries delete all types of cache queries.
SQL query statements that generate cached queries include:
SELECT:SELECTThe cache query is displayed in the catalog details of its table. If the query references more than one table, the same cache query is listed for each referenced table. A query that clears the cache from any of these tables clears it from all tables. From the table’s catalog details, you can select the cached query name to display the cached query details, including execution and display plan options. by$SYSTEM. SQL. Schema. ImportDDL (IRIS)The selection cache query created by the method is not providedExecuteandShow PlanOptions.
SELECT DECLARE NAME CURSOR Creates a cache query. However, the cached query details do not include execution and display plan options.
CALL: Creates the cache query shown in the cache query list for its schema.INSERT,UPDATE,INSERTorUPDATE,DELETE: creates its tableCatalog DetailsCache query displayed in.TRUNCATE TABLE: Creates a cache query for its table, which is displayed in the directory details.
Note that $SYSTEM. SQL. Schema. ImportDDL (” IRIS “) does not support truncation chart.
SET TRANSACTION.START TRANSACTION.%INTRANSACTION, COMMIT.ROLLBACK: Creates a cache query for each schema in the namespace, which is displayed in the cache query list.
When a query is prepared, a cached query is created. So, don’t%Prepare()It is important to put methods into the loop structure. A follow-up to the same query%Prepare()Use an existing cache query instead of creating a new cache query.
Changing the SetMapSelectability() value of the table invalidates any existing cached queries that reference the table. Subsequent preparation of the existing query creates a new cached query and removes the old cached query from the manifest.
When a cache query is cleared, the cache query is deleted. Changing the table definition automatically clears all queries that reference the table. Issuing a prepare or clear command automatically requests an exclusive system-wide lock when the query cache metadata is updated. The system administrator can modify the timeout value of the cache query lock.
The query that creates the cache is not part of the transaction. The creation of cached queries is not recorded.
Caching queries improves performance
When the query is first prepared, the SQL engine optimizes it and generates the program (a collection of one or more InterSystems IRIS®Data Platform routines) that will execute the query. The optimized query text is then stored as a cache query class. If you subsequently try to execute the same (or similar) query, the SQL engine will find the cached query and execute the code for that query directly, bypassing the need for optimization and code generation.
Cached queries provide the following benefits:
- Frequently used queries have faster subsequent execution. More importantly, this performance improvement can be achieved automatically without writing tedious stored procedures. Most relational database products recommend using only stored procedures to access the database. This is not required for IRIS.
- A single cached query is used for similar queries that differ only in literals. 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', justtopand%startswithThe text value of the condition is different. Cached queries prepared for the first query are automatically used for the second query. - Query caching is shared among all database users; If user 1 prepares a query, user 1023 can take advantage of it.
- The query optimizer is free to spend more time finding the best solution for a given query, since this cost only needs to be paid when the query is first prepared.
InterSystems SQL stores all cached queries in one location, the IRISLOCALDATA database. However, cached queries are namespace-specific. Each cached query is identified by the namespace that prepares (generates) it. Cached queries can only be viewed or executed from the namespace where the cached query is prepared. You can clear cached queries for the current namespace or all namespaces.
Cached queries do not include comments. However, it can include comment OPTIONS after the query text, such as /*#OPTIONS {“optionName”:value} */.
Because the cached query uses an existing query plan, it provides continuity of operations for the existing query. Changes to the underlying tables, such as adding indexes or redefining table optimization statistics, do not have any impact on existing cache queries.
Create a cache query
When InterSystems IRIS prepares a query, it determines:
- If the query matches an existing query in the query cache. If not, an increment count is assigned to the query.
- If the query is successfully prepared. If not, the increment count is not assigned to the cached query name.
- Otherwise, the increment count is assigned to the cached query name, and the query is cached.
Cache query name for dynamic SQL
The SQL engine assigns a unique class name to each cache query in the following format:
%sqlcq.namespace.clsnnn
Copy the codeNAMESPACE is the current NAMESPACE (uppercase) and NNN is a continuous integer. For example, % sqlcq.user.cls16.
Cached queries are numbered sequentially based on each namespace, starting with 1. The next available NNN sequence number depends on the reserved or released number:
- If the query does not match an existing cache query, a number is left when the query is prepared. If the query differs from an existing cache query only in literal values, the query matches the existing cache query – depending on some other considerations: hidden text substitution, different comment options, or what is described in “Separate cache Queries.”
- If the query fails, the number is reserved but not allocated. Only queries that are prepared successfully are cached.
- If the cache query is successfully prepared, a number is kept and assigned to the cache query. The cache query is listed for each table referenced in the query, regardless of whether any data is accessed from the table. If the query does not reference any tables, a cache query is created, but it cannot be listed or cleared by table.
- A number is released when the cache query is cleared. The number will be the next
NNNSerial numbers are available. Clearing a single cache query associated with a table or all cache queries for a table frees the number assigned to those cache queries. Clearing all cached queries in the namespace frees all numbers assigned to cached queries, including cached queries for unreferenced tables, as well as reserved but unassigned numbers.
Clearing the cache query resets the NNN integer. Integers are reused, but the remaining cache queries are not renumbered. For example, a partial purge of a cached query might leave CLS1, CLS3, CLS4, and CLS7 behind. Subsequent cache queries will be numbered CLS2, CLS5, CLs6, and CLS8.
A CALL statement can result in multiple cache queries. For example, the SQL statement CALL sample. PersonSets(‘A’,’MA’) generates the following cache query:
%sqlcq.USER.cls1: CALLSample . 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
Copy the codeIn dynamic SQL, after preparing the SQL query (using the %PrepareClassQuery() or %PrepareClassQuery() instance method), You can use the %display() instance method or the %GetImplementationDetails() instance method to return the cached query name. View the result of successful preparation.
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). The following example shows both methods of determining the cache query name:
/// w ##class(PHA.TEST.SQL).CacheQuery()
ClassMethod CacheQuery(c)
{
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(a)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 }Copy the codeDHC-APP>w ##class(PHA.TEST.SQL).CacheQuery(a)cached query name is: %sqlcq.DHCdAPP.cls51
result set OREF: 5 @ %sqlcq.DHCdAPP.cls51
Name AgeYao Xin 7O'Rielly.Chris H7.Orwell.John V4.Zevon.Heloisa O11.Smith.Kyra P6. 7Rows(s) Affected
A sample of 6 rows.with ageThe < 19Copy the codeIn this case, the selected number of rows (TOP clause) and WHERE clause predicate values change with each query call, but the cached query name does not change.
Cache query name for embedded SQL
The SQL engine assigns a unique class name to each embedded SQL cache query in the following format:
%sqlcq.namespace.hash
Copy the codeWhere NAMESPACE is the current NAMESPACE (uppercase) and HASH is the unique HASH value. For example, % SQLCQ. USER. XEM1h5QIeF4l3jhLZrXlnThVJZDh.
The management portal lists embedded SQL cache queries for each table, and directory Details lists cached queries with this class name for each table, and the query type is embedded cached SQL.
A separate cache query
Differences between two queries that should not affect query optimization will still generate separate cached queries:
- Different syntactic forms of the same function generate separate cached queries. As a result,
ASCII (" x ")and{fn ASCII (' x ')}Generate a separate cache query, while{fn CURDATE()}and{fn CURDATE}Generate a separate cache query. - The presence or absence of a case-sensitive table alias or column alias value and the optional AS keyword generates a separate cache query. As a result,
ASCII('x').ASCII('x') AChar, andASCII('x') AS ACharA separate cache query is generated. - Use different
ORDER BYClause. - use
top allInstead of having integer valuestop.
Words to replace
When the SQL engine caches an SQL query, it performs literal substitution. Queries in the query cache use “?” Character that represents an input parameter. This means that queries that differ only in literal values are represented by a single cached query. For example, two queries:
SELECT TOP 11 Name FROM Sample.Person WHERE Name %STARTSWITH 'A'
Copy the codeSELECT TOP 5 Name FROM Sample.Person WHERE Name %STARTSWITH 'Mc'
Copy the codeAre represented by a single cache query:
SELECT TOP ? Name FROM Sample.Person WHERE Name %STARTSWITH ?
Copy the codeThis minimizes the size of the query cache and means that query optimization does not need to be performed on queries that differ only in literals.
Use the input host variables (for example, myvar) and? Input parameters are also used as “? “in the corresponding cache query. “Character. SELECT Name FROM t1 WHERE Name=’Adam’ and Name=? , and SELECT Name FROM T1 WHERE Name=:namevar
You can use the %GetImplementationDetails() method to determine which of these entities are represented by each “? A character specified for preparation.
The following considerations apply to text substitution:
- The plus and minus signs specified as part of the text will generate a separate cache query. As a result,
ABS(7),ABS(-7)andABS(+7)Each generates a separate cache query. Multiple symbols also generate separate cache queries:ABS (+)?.ABS (a + +?. Therefore, it is best to use unsigned variablesABS (?). orABS(：Num)Can be supplied with signed or unsigned numbers without generating a separate cache query. - Precision and small values usually do not accept literal substitution. As a result,
ROUND (567.89, 2)Be cached forROUND (? , 2). However,CURRENT_TIME(N),CURRENT_TIMESTAMP(N),GETDATE(N)andGETUTCDATE(N)The optional precision value in does not accept literal substitution. IS NULLorIS NOT NULLThe text used in the condition does not accept text substitution.ORDER BYAny text used in the clause will not accept text substitution. This is becauseORDER BYYou can specify column positions using integers. Changing this integer will result in a fundamentally different query.- Alphabetic text must be enclosed in single quotation marks. Some functions allow you to specify an alphabetic format code with or without quotes; Only quoted alphabetic formatting codes accept literal substitution. As a result,
DATENAME (MONTER, 64701)andDATENAME(‘MONTER’，64701)Functionally the same, but the corresponding cache query isDATENAME (MONTER,?. andDATENAME (? ,?) - Functions that take a variable number of arguments generate a separate cache query for each parameter count. As a result,
Coalesce (1, 2)andCoalesce(1，2，3)A separate cache query is generated.
DynamicSQLTypeList Comment Option
When a query is matched, the comment option is treated as part of the query text. Therefore, queries in the annotation option that are different from existing cache queries do not match existing cache queries. Comment options can be specified by the user as part of the query, or they can be generated and inserted by the SQL preprocessor before the query is prepared.
If the SQL query contains a literal value, the SQL preprocessor generates the DynamicSQLTypeList annotation option and appends it to the end of the cached query text. This comment option assigns a data type to each literal. The data types are listed in the order in which the text appears in the query. Just list the actual text, not enter the host variable or? Input parameters. Here’s a typical example:
SELECT TOP 2 Name,Age FROM Sample.MyTest WHERE Name %STARTSWITH 'B' AND Age > 21.5
Copy the codeGenerate cached query text:
SELECT TOP ? Name , Age FROM Sample . MyTest WHERE Name %STARTSWITH ? AND Age > ? / * # OPTIONS {" DynamicSQLTypeList ":" 10,1,11} * /
Copy the codeIn this case, literal 2 is listed as type 10(integer), literal “B” is listed as type 1(string), and literal 21.5 is listed as type 11(number).
Note that the data type assignment is based only on the literal value itself, not the data type of the associated field. For example, in the example above, Age is defined as the data type INTEGER, but the literal value 21.5 is listed as NUMERIC. Because InterSystems IRIS converts numbers to canonical form, the literal value 21.0 is listed as an integer, not a number.
DynamicSQLTypeList returns the following data type values:
| digital | describe |
|---|---|
| 1 | The value is a string of 1 to 32 characters |
| 2 | The value is a string of 33 to 128 characters |
| 3 | The value is a string of 129 to 512 characters |
| 4 | The value is a string of more than 512 characters |
| 10 | Integer |
| 11 | Numeric |
Because the DynamicSQLTypeList annotation option is part of the query text, changing the text to produce a different data type causes a separate cache query to be created. For example, increase or decrease the length of a literal string so that it falls into different ranges.
Text replacement and performance
The SQL engine performs literal substitution on each value of the IN predicate. A large number of IN predicate values can have a negative impact on cached query performance. A variable number of IN predicate values can result IN multiple cache queries. Converting the IN predicate to the %INLIST predicate results IN the predicate having only one literal substitution, regardless of the number of listed values. %INLIST also provides an order of magnitude size parameter that SQL uses to optimize performance.
Cancel text substitution
You can cancel this substitution. In some cases, you may want to optimize literal values and create a separate cache query for queries that have that literal value. To cancel literal substitution, enclose the literal value in double parentheses. The following example shows this:
SELECT TOP 11 Name FROM Sample.Person WHERE Name %STARTSWITH (('A'))
Copy the codeSpecifying a different %STARTSWITH value will generate a separate cache query. Note that forbidding literal substitution is specified separately for each literal. In the example above, specifying different TOP values does not generate separate cached queries.
To cancel literal substitution for signed numbers, specify a syntax such as ABS(-((7)).
Note: In some cases, a different number of parentheses may also inhibit text substitution. InterSystems recommends always using double parentheses as the clearest and most consistent syntax for this purpose.
Share comment options
If an SQL query specifies multiple split tables, the SQL preprocessor generates a co-sharded comment option and appends it to the end of the cached query text. This co-sorting option shows whether the specified table is co-sorted.
In the following example, all three specified tables are encoded and shared:
/*#OPTIONS {"Cosharding":[["T1","T2","T3"]]} */
Copy the codeIn the following example, none of the three tables specified is encoded and shared:
/*#OPTIONS {"Cosharding":[["T1"],["T2"],["T3"]]} */
Copy the codeIn the following example, table T1 is uncategorized, but tables T2 and T3 are categorized:
/*#OPTIONS {"Cosharding":[["T1"],["T2","T3"]]} */
Copy the code