Chapter 5 Optimizing Query Performance (2)

Using the index

Indexes provide a mechanism to optimize queries by maintaining a sorted subset of common request data. Determining which fields should be indexed requires some thought: too few or wrong indexes and key queries will run too slowly; Too many indexes can degrade insert and update performance (because index values must be set or updated).

What index

To determine whether adding indexes improves query performance, run the query from the MANAGEMENT portal SQL interface and note the number of global references in performance. Add the index, then rerun the query, noting the number of global references. A useful index should reduce the number of global references. You can prevent the use of indexes by using the %NOINDEX keyword before a WHERE or ON clause condition.

The fields (attributes) specified in the join should be indexed. The left outer join starts from the left table and looks at the right table; Therefore, the fields in the right table should be indexed. In the following example, t2.f2 should be indexed:

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

An inner join should have indexes ON both ON clause fields.

Perform Show Plan and find the first map. If the first item in the query plan is a “Read Master map”, or if the first item in the module called by the query plan is a “Read Master Map”, the first mapping of the query is a master map, not an index map. Since the master map reads the data itself, rather than the data index, this always indicates an inefficient query plan. Unless the table is relatively small, you should create an index so that the first map of the query plan says “Read index map” when you rerun the query.

The fields specified in the WHERE clause equal condition should be indexed.

You might want to index fields specified in the WHERE clause scope condition, as well as fields specified in the GROUP BY and ORDER BY clauses.

In some cases, indexing based on range criteria can slow down queries. This can happen if the vast majority of rows satisfy specified range conditions. For example, if you use the QUERY clause WHERE Date < CURRENT_DATE for a database WHERE most of the records are from previous dates, indexing on Date might actually slow down the QUERY. This is because the query optimizer assumes that the range condition will return a relatively small number of rows and optimizes for this situation. You can determine if this happens by prefacing the scope condition with %noindex, and then run the query again.

If a comparison is performed using index fields, the collation type of the field specified in the comparison should be the same as the collation type in the corresponding index. For example, the Name field in the WHERE clause of a SELECT or 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 inefficient or may not be used at all.

Index configuration options

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

  • The primary key is used asIDKeyIndex, please set$SYSTEM.SQL.Util.SetOption()Method, as shown belowSET status=$SYSTEM.SQL.Util.SetOption("DDLPKeyNotIDKey",0,.oldval). The default is1
  • The index is used forSELECT DISTINCTQuery, please set$SYSTEM.SQL.Util.SetOption()Method, as follows:SET status=$SYSTEM.SQL.Util.SetOption("FastDistinct",1,.oldval). The default is1

Index usage analysis

You can analyze index usage by SQL cache query using either of the following methods:

  • Management portal index analyzer SQL performance tool.
  • %SYS.PTools.UtilSQLAnalysismethodsindexUsage(),tableScans(),tempIndices(),joinIndices()andoutlierIndices(). ,

Index analysis

You can analyze the index usage of SQL queries from the management portal using either of the following methods:

  • Select System Explorer, select Tools, select SQL Performance Tools, and then select Index Analyzer.
  • Select System Explorer, select SQL, and then select Index Analyzer from the Tools drop down menu.

The index analyzer provides a display of SQL statement counts for the current namespace and five index analysis report options.

SQL statement count

At the top of the SQL indexing analyzer is an option to count all SQL statements in the namespace. Press the Collect SQL statement button. The SQL index analyzer displays “Collecting SQL statements…” When the votes are counted, then “Done!” When the count is done. SQL statements are divided into three categories for counting: cache query counting, class method counting, and class query counting. These counts are for the entire current namespace and are not affected by schema selection options.

Corresponding method is % SYS. PTools. UtilSQLAnalysis getSQLStmts in class ().

You can use the Clear statement button to delete all statements collected in the current namespace. This button calls the clearSQLStatements() method.

The report options

You can check cached query reports for selected schemas in the current namespace or (by not selecting schemas) for all cached query reports in the current namespace. System class queries, INSERT statements, and/or IDKEY indexes can be skipped or included in this analysis. The Schema Selection and Skip Option check boxes are user-defined.

Index analysis report options include:

  • Index usage: This option gets all cached queries in the current namespace, generates a display plan for each query, and records how many times each index is used by each query and the total usage of each index by all queries in the namespace. This can be used to display unused indexes so that they can be dropped or modified to make them more useful. The result set is sorted from the least used index to the most used index.

Corresponding method is % SYS. PTools. UtilSQLAnalysis indexUsage in class (). To export analysis data generated by this method, use the exportIUAnalysis() method.

  • Queries that use a table scan: This option identifies all queries in the current namespace that perform a table scan. Avoid table scans if possible. Table scans are not always avoidable, but if a table has a large number of table scans, the indexes defined for that table should be checked. In general, table scan lists and temporary index lists overlap; Fixing one will delete the other. The result set lists tables in order from the largest chunk count to the smallest chunk count. The Show plan link is provided to display the statement text and query plan.

Corresponding method is % SYS. PTools. UtilSQLAnalysis tableScans in class (). To export the analysis data generated by this method, use the exportTSAnalysis() method.

  • Queries with temporary indexes: This option identifies all queries in the current namespace where temporary indexes are built to resolve SQL. Sometimes it is helpful and improves performance to use temporary indexes, such as building a small index based on range conditions that InterSystems IRIS can then use to read the master map sequentially. Sometimes, temporary indexes are just subsets of different indexes and can be very effective. In other cases, temporary indexes can degrade performance, for example, scanningmaster mayTo build temporary indexes on conditional properties. This indicates that the required index is missing; Indexes should be added to classes that match temporary indexes. The result set lists tables in order from the largest chunk count to the smallest chunk count. The Show plan link is provided to display the statement text and query plan.

Corresponding method is % SYS. PTools. UtilSQLAnalysis tempIndices in class (). To export the analysis data generated by this method, use the exportTIAnalysis() method.

  • Queries that lack a join index: This option checks all queries in the current namespace that have a join and determines if an index that supports the join is defined. It sorts the indexes that can be used to support joins from 0(where no indexes exist) to 4(where indexes fully support joins). External joins require a one-way index. An inner join requires a bidirectional index. By default, the result set contains onlyJoinIndexFlag<4The line.JoinIndexFlag=4Represents an index that fully supports joins.

The corresponding method is % SYS. PTools. UtilSQLAnalysis joinIndices in class (), it describes the JoinIndexFLAG value. To export the analysis data generated by this method, use the exportJIAnalysis() method. By default, exportJIAnalysis() does not list JoinIndexFlag=4 values, but can optionally list them.

  • Queries with outlier indexes: This option identifies all queries in the current namespace that have an outlier and determines whether an index that supports that outlier is defined. It will be available to support outlier indexes from0No index exists4(Index fully supports outliers) to sort. By default, the result set contains onlyOutlierIndexFlag<4The line.OutlierIndexFlag=4Indicates that an index that fully supports outliers exists.

Corresponding method is % SYS. PTools. UtilSQLAnalysis outlierIndices in class (). To export the analysis data generated by this method, use the exportOIAnalysis() method. By default, exportOIAnalysis() is not listed OutlierIndexFlag=4 values, but these values can be optionally listed.

When one of the options is selected, the system automatically performs the operation and displays the result. The first time an option is selected or the corresponding method is called, the system generates the resulting data; If you select this option or call the method again, InterSystems IRIS will redisplay the same results. To generate new result data, the Index Analyzer result table must be reinitialized using the Gather SQL Statements button. Generate new result data for % sys.ptools. In the UtilSQLAnalysis method, gettSQLSTmts () must be called to reinitialize the index analyzer result table. Changing the Skip all system classes and routines or skip insert Statement check box option also reinitializes the index analyzer result table.

IndexUsage () method

The following example demonstrates the use of the indexUsage() method:

/// w ##class(PHA.TEST.SQL).CountingCachedQueries2()
ClassMethod CountingCachedQueries2(a)
{
	DO ##class(%SYS.PTools.UtilSQLAnalysis).indexUsage(1, 1)SET utils = "SELECT %EXACT(Type), Count(*) As QueryCount "_
	          "FROM %SYS_PTools.UtilSQLStatements GROUP BY Type"
	SET utilresults = "SELECT SchemaName, Tablename, IndexName, UsageCount "_
	                "FROM %SYS_PTools.UtilSQLAnalysisDB ORDER BY UsageCount"
	SET tStatement = ##class(%SQL.Statement%).New(a)SET qStatus = tStatement.%Prepare(utils)
	IF qStatus'= 1 {WRITE "% Prepare failure:" DO $System. The Status. The DisplayError (qStatus) QUIT} SET rset = tStatement. % the Execute () DO rset.%Display() WRITE ! "Utility end data ",! SET qStatus = tStatement.%Prepare(utilresults) IF qStatus'=1 {
		WRITE "% Prepare failure."DO $System.Status.DisplayError(qStatus) QUIT } SET rset = tStatement.%Execute() DO rset.%Display() WRITE ! ."Utility end data"
}
Copy the code

Note that since the results are sorted by UsageCount, indexes with UsageCount > 0 are listed at the end of the result set.

Methods in this class can be called from ObjectScript or SQL calls or SELECT commands. The SQL naming convention is to specify the package name %SYS_PTools and then prefix the method name with “PT_” starting with a lowercase letter. See the following example:

ObjectScript:

  DO ##class(%SYS.PTools.UtilSQLAnalysis).indexUsage()
Copy the code

SQL:

  CALL %SYS_PTools.PT_indexUsage()
Copy the code
  SELECT %SYS_PTools.PT_indexUsage()
Copy the code

Index optimization options

By default, the InterSystems SQL query optimizer uses complex and flexible algorithms to optimize the performance of complex queries involving multiple indexes. In most cases, these default values provide the best performance. However, in rare cases, you may want to provide “hints” to the query optimizer by specifying the optimize-option keyword.

The %ALLINDEX and %IGNOREINDEX optime-option keywords are supported in the FROM clause. These optimization option keywords control all indexes used in the query.

You can make the %NOINDEX conditional level prompt specify exceptions to the use of indexes for specific conditions. The %NOINDEX prompt precedes each condition that the index should not be used. For example, WHERE %NOINDEX hiredate
), it is often beneficial to use the %NOINDEX conditional level hint. For equality conditions, using %NOINDEX conditional level hints does not provide any benefit. When a join condition is used, the ON clause join supports %NOINDEX.

The %NOINDEX keyword can be used to override index optimizations established in the FROM clause. In the following example, the %ALLINDEX optimization keyword applies to all 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
Copy the code