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

This list of SQL statements provide a record of SQL queries and other operations for each table, including table and index definition, insert, update, and delete. These SQL statements are linked to a query plan, and this link provides the option to freeze this query plan.

The system creates an SQL Statement for each compiled SQL operation. This provides a list of SQL DDL and DML operations listed by table, view, or procedure name. If you change the table definition, you can use this SQL Statements list to determine whether the query plan for each SQL operation will be affected by this DDL change and/or whether an SQL operation may need to be modified. You can then:
Note:
SQL Statements is a listing of SQL routines that may be affected by a change to a table definition. It should not be used as a history of changes to either the table definition or table data.
Operations that Create SQL Statements
The following SQL operations create corresponding SQL Statements:
Data definition (DDL) operations that compile an underlying Persistent Class always create (or update and re-compile) one or more SQL Statements. Multiple statements are defined if constraints such as UNIQUE and PRIMARY KEY are defined.
Data management (DML) operations include queries against the table, and insert, update, and delete operations. Each Embedded SQL data management (DML) operation creates an SQL Statement because Embedded SQL is compiled (by default) when the routine containing it is compiled. Dynamic SQL SELECT commands create an SQL Statement when the query is prepared and is preserved as a cached query. This creates a list of the most-recently compiled versions of all SQL commands. If a query references more than one table, a single SQL Statement is created that lists all of the referenced tables in the Table/View/Procedure Name(s) column.
Most SQL Statements have an associated Query Plan. When created, this Query Plan is unfrozen; you can subsequently designate this Query Plan as a frozen plan. SQL Statements with a Query Plan include DDL commands and DML commands that involve a SELECT operation. SQL Statements without a Query Plan are listed in the “Plan State” section below.
Note:
SQL Statements only lists the most recently compiled version of an SQL operation. Unless you freeze the SQL Statement, Caché replaces it with the next version. Thus rewriting the SQL code in a routine causes the old SQL code to disappear from SQL Statements.
Other SQL Statement Operations
The following SQL commands perform more complex SQL Statement operations:
Listing SQL Statements
Both listing interfaces specify the qualified table (or view) name, the plan state, the location of the routine that defined this statement (truncated to 128 characters), and the SQL Statement text. The SQL Statement text is in normalized format, which may differ from the command text, as specified below.
You can use the SQLTableStatements() catalog query or INFORMATION_SCHEMA.STATEMENTS to list SQL Statements selected by various criteria, as described in Querying the SQL Statements, below.
Plan State
The Plan State lists one of the following:
SQL Statement Text
The SQL Statement text commonly differs from the SQL command because Caché normalizes lettercase and whitespace. Other differences are as follows:
If you issue a query from the Management Portal interface or the SQL Shell interface, the resulting SQL Statement differs from the query by preceding the SELECT statement with DECLARE Q1 CURSOR FOR (where “Q1” can be a variety of generated cursor names). This allows the statement text to match that of the Dynamic SQL cached query.
If the SQL command specifies an unqualified table or view name, the resulting SQL Statement includes the default schema name.
SQL Statement Text is truncated after 1024 characters. To view the complete SQL Statement Text, display the SQL Statement Details.
A single SQL command may result in more than one SQL Statement. For example, if a query references a view, SQL Statements displays two statement texts, one listed under the view name, the other listed under the underlying table name. Freezing either statement results in Plan State of Frozen for both statements.
Data Management (DML) SQL Statements
The Data Management Language (DML) commands that create an SQL Statement are: INSERT, UPDATE, INSERT OR UPDATE, DELETE, TRUNCATE TABLE, SELECT, and DECLARE CURSOR FOR SELECT. You can use Dynamic SQL or Embedded SQL to compile (or prepare) a DML command. A DML command can be compiled for a table or a view, and Caché creates a corresponding SQL Statement.
Note:
The system creates an SQL Statement when Dynamic SQL is prepared or when Embedded SQL is compiled, not when the SQL is executed. The SQL Statement timestamp records when the SQL code was prepared or compiled, not when (or if) it was executed. Thus an SQL Statement may represent a change to the table that was never actually performed.
Preparing a Dynamic SQL DML command creates a corresponding SQL Statement. The Location associated with this SQL Statement is a cached query. Dynamic SQL is prepared when SQL is executed from the Management Portal SQL interface, from the SQL Shell interface, or imported from a .txt file. Purging an unfrozen cached query deletes the corresponding SQL Statement. Purging a frozen cached query removes the Location value for the corresponding SQL Statement; the SQL Statement is deleted when it is unfrozen.
Compiling a non-cursor Embedded SQL Data Management Language (DML) command creates a corresponding SQL Statement. Each Embedded SQL DML command creates a corresponding SQL Statement. If a routine contains multiple Embedded SQL commands, each Embedded SQL command creates a separate SQL Statement. (Some Embedded SQL commands create multiple SQL Statements.) The Location column of the SQL Statement listing specifies the routine that contains the Embedded SQL. In this way, SQL Statements maintains a record of each Embedded SQL DML command.
Compiling a cursor-based Embedded SQL Data Management Language (DML) command creates an SQL Statement for DECLARE CURSOR with a Query Plan. Associated Embedded SQL statements (OPEN cursor, FETCH cursor, CLOSE cursor) do not generate separate SQL Statements. Following a FETCH cursor, an associated UPDATE table WHERE CURRENT OF cursor or DELETE FROM table WHERE CURRENT OF cursor does generate a separate SQL Statement, but no separate Query Plan.
An INSERT command that inserts literal values creates a SQL Statement with the Plan State column blank. Because this command does not create a Query Plan, the SQL Statement cannot be frozen.
Modifying a Routine Containing Embedded SQL
When you compile a routine containing Embedded SQL, each SQL command is recorded as an entry in a statement dictionary for that routine. If you change and re-compile the routine, Caché removes all of the previous statement dictionary entries for that routine from all tables, then creates new entries corresponding to the current Embedded SQL content. If the re-compiled routine contains no Embedded SQL, the prior SQL Statements are removed and no new SQL Statements are added. Thus, by default, only the most recently compiled version of the Embedded SQL in a routine is preserved as an SQL Statement.
You can prevent a re-compile from deleting/replacing an SQL Statement by designating it as a Frozen Plan. This allows you to retain the query plan for that statement. Subsequent changes to the routine’s SQL code have no effect on frozen SQL Statements.
SELECT Commands
Compiling (or Preparing) a query creates a corresponding SQL Statement. It can be a simple SELECT, or a CURSOR-based SELECT/FETCH operation. The query can be issued against a table or a view.
SQL Statement Details
Clicking on an SQL Statement text displays the SQL Statement Details box. You can use this box to freeze or unfreeze a plan.
In addition to the complete Statement Text, the Query Plan, and the buttons to Freeze or Unfreeze the query plan, this box contains the following information:
Statement Details Section
Statement Details section:
Performance Statistics
Executing a query adds performance statistics to the corresponding SQL Statement. This information can be used to determine which queries are the slowest and which queries are executed the most. By using this information you can determine which queries would provide significant benefits by being optimized.
In addition to the SQL Statement name, Plan state, location, and text, the following additional information is provided for cached queries:
Query performance statistics are periodically updated for completed query executions. This minimizes the overhead involved in maintaining these statistics. As a consequence, currently running queries do not appear in the query performance statistics. Recently-completed queries (roughly, within the last hour) may not immediately appear in the query performance statistics.
Caché does not separately record performance statistics for %PARALLEL subqueries. %PARALLEL subquery statistics are summed with the statistics for the outer query. Queries generated by the implementation to run in parallel do not have their performance statistics tracked individually.
Caché does not record performance statistics for “natural” queries. If the system collected statistics it would slow the query performance, and a natural query is already optimal, so there is no potential for optimization.
You can view these query performance statistics via the SQL Statements in this Namespace tab from the Management Portal SQL interface. Note that you can sort this SQL Statement listing by any column. This makes it easy to determine, for example, which queries have the largest average time.
You can also access these query performance statistics by querying the INFORMATION.SCHEMA.STATEMENTS class properties, as described in Querying the SQL Statements.
Compile Settings Section
Compile Settings section:
Routines and Relations Sections
Statement is defined in the following routines section:
Statement uses the following relations section lists one or more defined tables used to create the query plan. For an INSERT that uses a query to extract values from another table, or an UPDATE or DELETE that uses a FROM clause to reference another table, both tables are listed here. For each table the following values are listed:
This section includes an option to re-compile the class. If you re-compile an unfrozen plan, all three time fields are updated. If you re-compile a frozen plan, the two Last Compile Time fields are updated, but the Plan Timestamp is not. When you unfreeze the plan and click the Refresh Page button, the Plan Timestamp updates to the time the plan was unfrozen.
Data Definition (DDL) SQL Statements
Creating a table compiles a corresponding Persistent Class, and therefore creates one or more SQL Statements. Creating a view does not create a persistent class, so no SQL Statements are created.
You can create an SQL table by defining it as a persistent class, or defining it using the SQL CREATE TABLE command from either Embedded SQL or Dynamic SQL. Regardless of how you create a table, the system creates one or more corresponding SQL Statements. The Location specifies the class name associated with the table definition.
When you create a table, Caché SQL defines a bitmap extent index. In doing so, it creates a corresponding SQL Statement, such as the following:
DECLARE QEXTENT CURSOR FOR SELECT ID FROM SAMPLE . SQLTEST
Altering a table definition recompiles the existing create table SQL Statements.
When you create an index, either by modifying the table class definition or by issuing an SQL CREATE INDEX command, Caché stores a corresponding SQL Statement, such as the following, for each created index. The SQL Statement is the same regardless of the type of index created. Creating more than one type of index for a field does not store additional SQL statements:
SELECT %ID INTO :id FROM SAMPLE . SQLTEST WHERE ( :K1 IS NOT NULL AND LASTNAME = :K1 ) OR ( :K1 IS NULL AND LASTNAME IS NULL )
Adding an index also causes all of the create table SQL Statements to be re-compiled, updating the Plan Timestamp for all of them, including SQL Statements for other indices. A DROP INDEX removes this SQL Statement and causes all of the remaining DDL SQL Statements to be re-compiled, updating the Plan Timestamp.
If you define a Primary Key or Unique constraint, Cache defines a primary key index, and therefore creates an SQL Statement such as the one above. In addition to the two SQL Statements described above, Caché adds the following four statements (in this example the LastName field):
SELECT 1 AS _PASSFAIL FROM SAMPLE . SQLTEST WHERE LASTNAME = :pValue(1) AND %ID <> :id 
SELECT LASTNAME INTO :tCol1 FROM SAMPLE . SQLTEST WHERE %ID = :pID  
DECLARE EXT CURSOR FOR SELECT %ID INTO :tID FROM SAMPLE . SQLTEST 
SELECT %ID INTO :id FROM SAMPLE . SQLTEST WHERE LASTNAME = :%d(2) 
Each additional UNIQUE field adds three more SQL statements like these: specifying in the WHERE clause the UNIQUE field (In this example the SSN field).
SELECT %ID INTO :id FROM SAMPLE . SQLTEST WHERE ( :K1 IS NOT NULL AND SSN = :K1 ) OR ( :K1 IS NULL AND SSN IS NULL )
SELECT 1 AS _PASSFAIL FROM SAMPLE . SQLTEST WHERE SSN = :pValue(1) AND %ID <> :id
SELECT %ID INTO :id FROM SAMPLE . SQLTEST WHERE SSN = :%d(3)
It also modifies the following statement to include the additional UNIQUE field, such as the following:
SELECT SSN , LASTNAME INTO :tCol2 , :tCol1 FROM SAMPLE . SQLTEST WHERE %ID = :pID
Delete Table and SQL Statements
When a table is deleted, all non-frozen SQL Statements (Plan State Unfrozen) are deleted. Frozen statements (Plan State Frozen/Explicit) are not deleted, but the Table/View/Procedure Name(s) column is modified, as in the following example: SAMPLE.MYTESTTABLE - Deleted?? Sample.Person; the name of the deleted table is converted to all uppercase letters and is flagged as “Deleted??”. The Location column is blank for DDL statements because the table has been deleted. The Location column is blank for Dynamic SQL DML statements because all cached queries associated with the table have been automatically purged.
Querying the SQL Statements
You can use the SQLTableStatements() stored query to return the SQL Statements for a specified table. This is shown in the following example:
  ZNSPACE "Samples"
  SET mycall = "CALL %Library.SQLTableStatements('Sample','Person')"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus=tStatement.%Prepare(mycall)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset=tStatement.%Execute()
  IF rset.%SQLCODE '= 0 {WRITE "SQL error=",rset.%SQLCODE QUIT}
  DO rset.%Display()
 
You can use the INFORMATION_SCHEMA package tables to query the list of SQL Statements. Caché supports three classes (tables):
The following are some example queries using these classes:
The following example returns all the SQL Statements and the routines the statements are located in for a given SQL table. (Note that the table name (SAMPLE.PERSON) must be specified with the same letter case used in the SQL Statement text: all uppercase letters):
SELECT Statement,Frozen,STATEMENT_LOCATIONS->Location AS Routine,STATEMENT_LOCATIONS->Type AS RoutineType
       FROM INFORMATION_SCHEMA.STATEMENTS 
       WHERE STATEMENT_RELATIONS->Relation='SAMPLE.PERSON'
 
The following example returns all the SQL Statements in the current namespace that have frozen plans:
SELECT Statement,Frozen,STATEMENT_LOCATIONS->Location AS Routine,STATEMENT_LOCATIONS->Type AS RoutineType
       FROM INFORMATION_SCHEMA.STATEMENTS 
       WHERE Frozen=1
 
The following example returns all the SQL Statements in the current namespace that contain a COUNT(*) aggregate function. (Note that the statement text (COUNT ( * )) must be specified with the same whitespace used in the SQL Statement text):
SELECT Statement,Frozen,STATEMENT_LOCATIONS->Location AS Routine,STATEMENT_LOCATIONS->Type AS RoutineType
       FROM INFORMATION_SCHEMA.STATEMENTS 
       WHERE Statement [ ' COUNT ( * ) '