Optimizing Query Performance

Gather Query Performance Statistics

You must activate statistics (Stats) code generation to collect performance statistics, using either of the following:

• The Management Portal SQL Runtime Statistics tab. (From the Management Portal, select System Explorer, then Tools, then SQL Performance Tools, then SQL Runtime Statistics).

• The SetSQLStats()Opens in a new tab or SetSQLStatsJob()Opens in a new tab method.

For either of these interfaces, you specify one of the following options: 0 turn off statistics code generation; 1 turn on statistics code generation for all queries, but do not gather statistics (the default); 2 record statistics for just the outer loop of the query (gather statistics at the open and close of the query); 3 record statistics for all module levels of the query. Modules can be nested. If so, the MAIN module statistics are inclusive numbers, the overall results for the full query.

For SetSQLStatsJob() the options differ slightly. They include: -1 turn off statistics for this job; 0 use the system setting value. The 1, 2, and 3 options are the same as SetSQLStats() and override the system setting. The default is 0.

To gather SQL Stats data, queries need to be compiled with statistics code generation turned on (option 1, the default):

• To go from 0 to 1: after changing the SQL Stats option, runtime Routines and Classes that contain SQL will need to be compiled to perform statistics code generation. For xDBC and Dynamic SQL, you must purge cached queries to force code regeneration.

• To go from 1 to 2: you simply change the SQL Stats option to begin gathering statistics. This allows you to enable SQL performance analysis on a running production environment with minimal disruption.

• To go from 1 to 3 (or 2 to 3): after changing the SQL Stats option, runtime Routines and Classes that contain SQL will need to be compiled to record statistics for all module levels. For xDBC and Dynamic SQL, you must purge cached queries to force code regeneration. Option 3 is commonly only used on an identified poorly-performing query in a non-production environment.

• To go from 1, 2, or 3 to 0: to turn off statistics code generation you do not need to purge cached queries.

This information is stored in %SYS.PTools.SQLQueryOpens in a new tab and %SYS.PTools.SQLStatsOpens in a new tab.

Purging a cached query purges any related SQL Stats data. Dropping a table or view purges any related SQL Stats data.

Display Query Performance Statistics

You can display performance statistics for an SQL query as follows:

• From the Management Portal select System Explorer, then Tools, then select SQL Performance Tools, then SQL Runtime Statistics and click the View Stats tab. This gives you an overall view of the runtime statistics that have been gathered on this system.

  You can click on a View Stats column to sort the query statistics. You can then click Show Plan for a specific query.

• By using the GetLastSQLStats()Opens in a new tab method of the %SYS.PTools.SQLStatsOpens in a new tab class, as shown in the following example:

    ZNSPACE "Samples"
    DO $SYSTEM.SQL.SetSQLStatsJob(2)
    SET myquery = "SELECT TOP 5 Name,DOB FROM Sample.Person"
    SET tStatement = ##class(%SQL.Statement).%New()
    SET qStatus = tStatement.%Prepare(myquery)
      IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
    SET rset = tStatement.%Execute()
    DO rset.%Display()
    WRITE !,"end of query result set",!!
    KILL rset
    DO ##class(%SYS.PTools.SQLStats).GetLastSQLStats()
    DO %sqlcontext.DumpResults()

  

• By calling a stored procedure, as shown in the following example:

    ZNSPACE "Samples"
    DO $SYSTEM.SQL.SetSQLStatsJob(2)
    SET myquery = "SELECT TOP 5 Name,DOB FROM Sample.Person"
    SET tStatement = ##class(%SQL.Statement).%New()
    SET qStatus = tStatement.%Prepare(myquery)
      IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
    SET rset = tStatement.%Execute()
    DO rset.%Display()
    WRITE !,"end of query result set",!!
    KILL rset
    &sql(CALL %SYS_PTools.GetLastSQLStats())
    DO %sqlcontext.DumpResults()

  

The following examples collect runtime statistics from an INSERT statement:

CreateATable
  &sql(CREATE TABLE sample.sqltest (FullName VARCHAR(25),MyDate DATE DEFAULT CURRENT_DATE))
    IF SQLCODE=0 { WRITE "sqltest table created",! }
    ELSE {WRITE "table create failed SQLCODE=",SQLCODE,! }

InsertData
  SET oldstat=$SYSTEM.SQL.SetSQLStatsJob(2)
  &sql(INSERT INTO sample.sqltest(FullName) SELECT Name FROM Sample.Person WHERE Name BETWEEN 'A' AND 'J')
  WRITE "Inserted ",%ROWCOUNT," rows in table SQLCODE=",SQLCODE,!
  DO ##class(%SYS.PTools.SQLStats).GetLastSQLStats()
  DO %sqlcontext.DumpResults()
Cleanup
   DO $SYSTEM.SQL.SetSQLStatsJob(oldstat)
   &sql(DROP TABLE sample.sqltest)

You can use the SQLStatsView query to display these statistics, as shown in the following example:

  ZNSPACE "Samples"
  DO ##class(%SYS.PTools.SQLStats).Purge("Samples")
  DO ##class(%SYSTEM.SQL).SetSQLStatsJob(2)
  SET myquery = "SELECT TOP 5 Name,DOB FROM Sample.Person"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"end of query result set",!!
  KILL rset
  DO ##class(%ResultSet).RunQuery("%SYS.PTools.SQLStats","SQLStatsView")
  WRITE !!,"End of SQL Statistics"

Runtime Statistics and Show Plan

The SQL Runtime Statistics tool can be used to display the Show Plan for a query with runtime statistics.

The Alternate Show Plans tool can be used to compare show plans with stats, displaying runtime statistics for a query. The Alternate Show Plans tool in its Show Plan Options displays estimated statistics for a query. If gathering runtime statistics is activated, its Compare Show Plans with Stats option displays actual runtime statistics; if runtime statistics are not active, this option displays estimate statistics.

What to Index

To determine if adding an index improves query performance, run the query from the Management Portal SQL interface and note in Performance the number of global references. Add the index and then rerun the query, noting the number of global references. A useful index should reduce the number of global references. You can prevent use of an index by using the %NOINDEX keyword as preface to a WHERE clause or ON clause condition.

You should index fields (properties) that are specified in a JOIN. A LEFT OUTER JOIN starts with the left table, and then looks into the right table; therefore, you should index the field from the right table. In the following example, you should index T2.f2:

   FROM Table1 AS T1 LEFT OUTER JOIN Table2 AS T2 ON T1.f1 = T2.f2

An INNER JOIN should have indices on both ON clause fields.

Run Show Plan and follow to the first map. If the first bullet item in the Query Plan is “Read master map”, or the Query Plan calls a module whose first bullet item is “Read master map”, the query first map is the master map rather than an index map. Unless the table is relatively small, you should create an index so that when you rerun this query the Query Plan first map says “Read index map.”

You should index fields that are specified in a WHERE clause equal condition.

You may wish to index fields that are specified in a WHERE clause range condition, and fields specified in GROUP BY and ORDER BY clauses.

Under certain circumstances, an index based on a range condition could make a query slower. This can occur if the vast majority of the rows meet the specified range condition. For example, if the query clause WHERE Date < CURRENT_DATE is used with a database in which most of the records are from prior dates, indexing on Date may actually slow down the query. This is because the Query Optimizer assumes range conditions will return a relatively small number of rows, and optimizes for this situation. You can determine if this is occurring by prefacing the range condition with %NOINDEX and then run the query again.

If you are performing a comparison using an indexed field, the field as specified in the comparison should have the same collation type as it has in the corresponding index. For example, the Name field in the WHERE clause of a SELECT or in the ON clause of a JOIN should have the same collation as the index defined for the Name field. If there is a mismatch between the field collation and the index collation, the index may be less effective or may not be used at all. For further details, refer to Index Collation in the “Defining and Building Indices” chapter of this manual.

For details on how to create an index and the available index types and options, refer to the CREATE INDEX command in the Caché SQL Reference, and the “Defining and Building Indices” chapter of this manual.

Index Configuration Options

The following system-wide configuration methods can be used to optimize use of indices in queries:

• $SYSTEM.SQL.SetDDLPKeyNotIDKey()Opens in a new tab to use the PRIMARY KEY as the IDKey index.

• $SYSTEM.SQL.SetFastDistinct()Opens in a new tab to use indices for SELECT DISTINCT queries.

For further details, refer to SQL configuration settings described in Caché Advanced Configuration Settings Reference.

Index Usage Analysis

You can analyze index usage by SQL cached queries using either of the following:

• The Management Portal Index Analyzer SQL performance tool.

• The %SYS.PTools.SQLUtilitiesOpens in a new tab methods IndexUsage()Opens in a new tab, TableScans()Opens in a new tab, TempIndices()Opens in a new tab, and JoinIndices()Opens in a new tab.

Index Analyzer

From the Management Portal Tools interface, select System Explorer, then Tools, then select SQL Performance Tools, then Index Analyzer. It provides an SQL Statement Count display for the current namespace, and four index analysis report options.

IndexUsage() Method

The following example demonstrates the use of the IndexUsage()Opens in a new tab method:

  ZNSPACE "Samples"
  DO ##class(%SYS.PTools.SQLUtilities).IndexUsage(1)
  SET utils = "SELECT %EXACT(Type), Count(*) As QueryCount "_
              "FROM %SYS_PTools.SQLUtilities GROUP BY Type"
  SET utilresults = "SELECT SchemaName, Tablename, IndexName, UsageCount "_
                    "FROM %SYS_PTools.SQLUtilResults ORDER BY UsageCount"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(utils)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of utilities data",!!
  SET qStatus = tStatement.%Prepare(utilresults)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of results data"

Note that because results are ordered by UsageCount, indices with UsageCount > 0 are listed at the end of the result set.

Index Optimization Options

By default, the Caché SQL query optimizer uses sophisticated and flexible algorithms to optimize the performance of complex queries involving multiple indices. In most cases, these defaults provide optimal performance. However, in infrequent cases, you may wish to give “hints” to the query optimizer by specifying optimize-option keywords.

The FROM clause supports the %ALLINDEX and %IGNOREINDEX optimize-option keywords. These optimize-option keywords govern all index use in the query. They are described in detail in the FROM clause reference page of the Caché SQL Reference.

You can use the %NOINDEX condition-level hint to specify exceptions to the use of an index for a specific condition. The %NOINDEX hint is placed in front of each condition for which no index should be used. For example, WHERE %NOINDEX hiredate < ?. This is most commonly used when the overwhelming majority of the data is selected (or not selected) by the condition. With a less-than (<) or greater-than (>) condition, use of the %NOINDEX condition-level hint is often beneficial. With an equality condition, use of the %NOINDEX condition-level hint provides no benefit. With a join condition, %NOINDEX is not supported for =* and *= WHERE clause outer joins; %NOINDEX is supported for ON clause joins.

The %NOINDEX keyword can be used to override indexing optimization established in the FROM clause. In the following example, the %ALLINDEX optimization keyword applies to all condition tests except the E.Age condition:

  SELECT P.Name,P.Age,E.Name,E.Age
  FROM %ALLINDEX Sample.Person AS P LEFT OUTER JOIN Sample.Employee AS E
       ON P.Name=E.Name
  WHERE P.Age > 21 AND %NOINDEX E.Age < 65

Displaying an Execution Plan

You can use Show Plan to display the execution plan for a query in any of the following ways:

• From the Management Portal SQL interface. Select System Explorer, then SQL. Select a namespace with the Switch option at the top of the page. (You can set the Management Portal default namespace for each user.) Write a query, then press the Show Plan button. (You can also invoke Show Plan from the Show History listing by clicking the plan option for a listed query.) See Executing SQL Statements in the “Using the Management Portal SQL Interface” chapter of this manual.

• From the Management Portal Tools interface: Select System Explorer, then Tools, then select SQL Performance Tools, then SQL Runtime Statistics:

  • From the Query Test tab: Select a namespace with the Switch option at the top of the page. Write a query in the text box. Then press the Show Plan with SQL Stats button. This generates a Show Plan without executing the query.

  • From the View Stats tab: Press the Show Plan button for one of the listed queries. The listed queries include both those written at Execute Query, and those written at Query Test.

• By running the ShowPlan()Opens in a new tab method, as shown in the following example:

    ZNSPACE "Samples"
    SET oldstat=$SYSTEM.SQL.SetSQLStatsJob(3)
    SET mysql=2
    SET mysql(1)="SELECT TOP 10 Name,DOB FROM Sample.Person "
    SET mysql(2)="WHERE Name [ 'A' ORDER BY Age"
    DO $SYSTEM.SQL.ShowPlan(.mysql,0,1)
    DO $SYSTEM.SQL.SetSQLStatsJob(oldstat)

  

• By running Show Plan against a cached query result set, using :i%Prop syntax for literal substitution values stored as properties:

    ZNSPACE "Samples"
    SET cqsql=2
    SET cqsql(1)="SELECT TOP :i%PropTopNum Name,DOB FROM Sample.Person "
    SET cqsql(2)="WHERE Name [ :i%PropPersonName ORDER BY Age"
    DO ShowPlan^%apiSQL(.cqsql,0,"",0,$LB("Sample"),"",1)

  

Show Plan by default returns values in Logical mode. However, when invoking Show Plan from the Management Portal or the SQL Shell, Show Plan uses Runtime mode.

Execution Plan: Statement Text and Query Plan

The Show Plan execution plan consists of two components, Statement Text and Query Plan:

Statement Text replicates the original SQL statement, with the following modifications: The Show Plan button from the Management Portal SQL interface displays a SELECT query prefaced with DECLARE QRS CURSOR FOR (QRS is Query Result Set). This is done to allow Show Plan to use a frozen plan. For all SQL statements, whitespace is standardized, comments are removed. The Show Plan button display also performs literal substitution, replacing each literal with a ?, unless you have suppressed literal substitution by enclosing the literal value in double parentheses. These modifications are not done when displaying a show plan using the ShowPlan() method, or when displaying alternate show plans.

Query Plan shows the plan that would be used to execute the query. A Query Plan can consist of the following:

• “Frozen Plan” is the first line of Query Plan if the query plan has been frozen; otherwise, the first line is blank.

• “Relative cost” is an integer value which is computed from many factors as an abstract number for comparing the efficiency of different execution plans for the same query. This calculation takes into account (among other factors) the complexity of the query, the presence of indices, and the size of the table(s). Relative cost is not useful for comparing two different queries. “Relative cost not available” is returned by certain aggregate queries, such as COUNT(*) or MAX(%ID) without a WHERE clause.

• The Query Plan consists of a main module, and (when needed) one or more subcomponents. One or more module subcomponents may be shown, named alphabetically, starting with B: Module B, Module C, etc.), and listed in the order of execution (not necessarily alphabetically). When the end of the alphabet is reached, additional modules are numbered, parsing Z=26, so the next module after Module Z is Module 27. A module performs processing and populates an internal temp-file (internal temporary table) with its results.

  One or more subquery subcomponents may be shown; each subquery is shown as a separate subquery module in the order specified in the query. Subquery modules are not named. If a subquery calls a module, the module is placed after the subquery and given an appropriate non-sequential alphabetical name. Therefore, a query plan could contain a main module that calls Module B and a Subquery that calls Module H.

• “Read master map” as the first bullet item in the main module indicates an inefficient Query Plan. The Query Plan begins execution with one of the following map type statements Read master map... (no available index), Read index map... (use available index), or Generate a stream of idkey values using the multi-index combination... (Multi Index, use multiple indices). Because the master map reads the data itself, rather than an index to the data, Read master map... almost always indicates an inefficient Query Plan. Unless the table is relatively small, you should define an index so that when you regenerate the Query Plan the first map says Read index map.... For information on interpreting a Query Plan, refer to “Interpreting an SQL Query Plan.”

Some operations create a Show Plan that indicates no Query Plan could be generated:

• Non-query INSERT: An INSERT... VALUES() command does not perform a query, and therefore does not generate a Query Plan.

• Query always FALSE: In a few cases, Caché can determine when preparing a query that a query condition will always be false, and thus cannot return data. The Show Plan informs you of this situation in the Query Plan component. For example, a query containing the condition WHERE %ID IS NULL or the condition WHERE Name %STARTSWITH('A') AND Name IS NULL cannot return data, and therefore Caché generates no execution plan. Rather than generating an execution plan, the Query Plan says “Output no rows”. If a query contains a subquery with one of these conditions, the subquery module of the Query Plan says “Subquery result NULL, found no rows”. This condition check is limited to a few situations involving NULL, and is not intended to catch all self-contradictory query conditions.

• Invalid query: Show Plan displays an SQLCODE error message for most invalid queries. However, in a few cases, Show Plan displays as empty. For example, WHERE Name = $$$$$ or WHERE Name %STARTSWITH('A") (note single-quote and double-quote). In these cases, Show Plan displays no Statement Text, and Query Plan says [No plan created for this statement]. This commonly occurs when quotation marks delimiting a literal are imbalanced. It also occurs when you specify two or more leading dollar signs without specifying the correct syntax for a user-defined (“extrinsic”) function.

Stats

The Show Plans Options lists assigns each alternate show plan a Cost value, which enables you to make relative comparisons between the execution plans.

The Alternate Show Plan details provides for each Query Plan a set of stats (statistics) for the Query Totals, and (where applicable) for each Query plan module. The stats for each module include Time (overall performance, in seconds), Globals (number of global references), Commands (number of commands executed), and Disk Wait (disk read latency, in milliseconds). The Query Totals stats also includes Rows (the number of rows returned).

%PARALLEL Keyword Ignored

Regardless of the presence of the %PARALLEL keyword in the FROM clause, some queries may use linear processing, not parallel processing. Caché makes the decision whether or not to use parallel processing for a query after optimizing that query, applying other query optimization options (if specified). Caché may determine that the optimized form of the query is not suitable for parallel processing, even if the user-specified form of the query would appear to benefit from parallel processing. You can determine if and how Caché has partitioned a query for parallel processing using Show Plan.

In the following circumstances specifying %PARALLEL does not perform parallel processing. The query executes successfully and no error is issued, but parallelization is not performed:

• The query contains the FOR SOME predicate.

• The query contains both a TOP clause and an ORDER BY clause. This combination of clauses optimizes for fastest time-to-first-row which does not use parallel processing. Adding the FROM clause %NOTOPOPT optimize-option keyword optimizes for fastest retrieval of the complete result set. If the query does not contain an aggregate function, this combination of %PARALLEL and %NOTOPOPT performs parallel processing of the query.

• A query containing a LEFT OUTER JOIN or INNER JOIN in which the ON clause is not an equality condition. For example, FROM %PARALLEL Sample.Person p LEFT OUTER JOIN Sample.Employee e ON p.dob > e.dob. This occurs because SQL optimization transforms this type of join to a FULL OUTER JOIN. %PARALLEL is ignored for a FULL OUTER JOIN.

• The %PARALLEL and %INORDER optimizations cannot be used together; if both are specified, %PARALLEL is ignored.

• COUNT(*) does not use parallel processing if the table has a BITMAPEXTENT index.

• %PARALLEL is intended for tables using standard data storage definitions. Its use with customized storage formats may not be supported. %PARALLEL is not supported for GLOBAL TEMPORARY tables or tables with extended global reference storage.

• %PARALLEL is intended for a query that can access all rows of a table, a table defined with row-level security (ROWLEVELSECURITYOpens in a new tab) cannot perform parallel processing.

• %PARALLEL is intended for use with data stored in the local database. It does not support global nodes mapped to a remote database.

%PARALLEL in Subqueries

%PARALLEL is intended for SELECT queries and their subqueries. An INSERT command subquery cannot use %PARALLEL.

%PARALLEL is ignored when applied to a subquery that is correlated with an enclosing query. For example:

SELECT name,age FROM Sample.Person AS p 
WHERE 30<(SELECT AVG(age) FROM %PARALLEL Sample.Employee where Name = p.Name)

%PARALLEL is ignored when applied to a subquery that includes a complex predicate, or a predicate that optimizes to a complex predicate. Predicates that are considered complex include the %CONTAINS, %CONTAINSTERM, FOR SOME, and FOR SOME %ELEMENT predicates.

Shared Memory Considerations

For parallel processing, Caché supports multiple InterProcess Queues (IPQ). Each IPQ handles a single parallel query. It allows parallel work unit subprocesses to send rows of data back to the main process so the main process does not have to wait for a work unit to complete. This enables parallel queries to return their first row of data as quickly as possible, without waiting for the entire query to complete. It also improves performance of aggregate functions.

Parallel query execution uses shared memory from the generic memory heap (gmheap). Users may need to increase gmheap size if they are using parallel SQL query execution. As a general rule, the memory requirement for each IPQ is 4 x 64k = 256k. Caché splits a parallel SQL query into the number of available CPU cores. Therefore, users need to allocate this much extra gmheap:

<Number of concurrent parallel SQL requests> x <Number cores> x 256 = <required size increase (in kilobytes) of gmheap>

Note that this formula is not 100% accurate, because a parallel query can spawn sub queries which are also parallel. Therefore it is prudent to allocate more extra gmheap than is specified by this formula.

Failing to allocate adequate gmheap results in errors reported to cconsole.log. SQL queries may fail. Other errors may also occur as other subsystems try to allocate gmheap.

To review gmheap usage by an instance, including IPQ usage in particular, from the home page of the Management Portal choose System Operation then System Usage, and click the Shared Memory Heap Usage link; see Generic (Shared) Memory Heap Usage in the “Monitoring Caché Using the Management Portal” chapter of the Caché Monitoring Guide for more information.

To change the size of the generic memory heap or gmheap (sometimes known as the shared memory heap or SMH), from the home page of the Management Portal choose System Administration then Configuration then Additional Settings then Advanced Memory; see Advanced Memory Settings in the “Caché Additional Configuration Settings” chapter of the Caché Additional Configuration Settings Reference for more information.

Cached Query Considerations

If you are running a cached SQL query which uses %PARALLEL and while this query is being initialized you do something that purges cached queries, then this query could get a <NOROUTINE> error reported from one of the worker jobs. Typical things that causes cached queries to be purged are calling $SYSTEM.SQL.Purge() or recompiling a class which this query references. Recompiling a class automatically purges any cached queries relating to that class.

If this error occurs, running the query again will probably execute successfully. Removing %PARALLEL from the query will avoid any chance of getting this error.

SQL Statements and Plan State

An SQL query which uses %PARALLEL can result in multiple SQL Statements. The Plan State for these SQL Statements is Unfrozen/Parallel. A query with a plan state of Unfrozen/Parallel cannot be frozen by user action. Refer to the “SQL Statements” chapter for further details.