Skip to main content

Specify Optimization Hints in Queries

By default, the InterSystems SQL query optimizer uses sophisticated and flexible algorithms to optimize the performance of complex queries involving multiple indexes. In nearly all cases, these defaults provide optimal performance. However, InterSystems SQL provides hints that can be used to manually modify the execution plan. You will most typically use these hints at the instruction of the InterSystems Worldwide Resource Center (WRC) to configure an optimal query execution plan. To get help from the WRC with SQL performance, refer to “Generate Report.”

There are two ways to provide such hints to the query optimizer to force it to employ certain optimizations or avoid others. The first is employing keywords in a FROM clause of a SELECT statement; the second is specifying comment options.

FROM Clause Keywords

SELECT statements can make use of the FROM clause, to which you may supply a keyword that will specify certain query optimization behaviors. Multiple keywords may be provided in any order, separated by blank spaces. Refer to “Query Optimization Options” for more information about each keyword.

The list of keywords you may supply is as follows:

  • %ALLINDEX – Specifies that all indexes that provide any benefit are used for the first table in the query join order.

  • %FIRSTTABLE – Specifies that the query optimizer should start to perform joins with a specified table name.

  • %FULL – Specifies that the compiler optimizer examines all alternative join sequences to maximize access performance.

  • %IGNOREINDEX – Specifies that the query optimizer ignore the specified index or list of indexes.

  • %INORDER – Specifies that the query optimizer performs joins in the order that the tables are listed.

  • %NOFLATTEN – Specifies that the query optimizer should inhibit subquery flattening.

  • %NOMERGE – Specifies that the query optimizer should inhibit the conversion of a subquery to a view.

  • %NOREDUCE – Specifies that the query optimizer should inhibit the merging to the subquery (or view) into the containing query.

  • %NOSVSO – Specifies that the query optimizer should inhibit Set-Valued Subquery Optimization.

  • %NOTOPOPT – Usable in queries with a TOP clause and an ORDER BY clause, this option optimizes the query for fastest retrieval of the complete result set.

  • %NOUNIONOROPT – Disables the automatic optimizations provided for multiple OR conditions and for subqueries against a UNION query.

  • %PARALLEL – Suggests that InterSystems IRIS perform parallel processing on the query.

  • %STARTTABLEOpens in a new tab – Specifies that the query optimizer should start to perform joins with the first table listed.

Comment Options

You can specify one or more comment options to the Query Optimizer within a SELECT, INSERT, UPDATE, DELETE, or TRUNCATE TABLE command. A comment option specifies a option that the query optimizer uses during the compile of the SQL query. Often a comment option is used to override a system-wide configuration default for a specific query.

Syntax

The syntax /*#OPTIONS */, with no space between the /* and the #, specifies a comment option. A comment option is not a comment; it specifies a value to the query optimizer. A comment option is specified using JSON syntax, commonly a key:value pair such as the following: /*#OPTIONS {"optionName":value} */. More complex JSON syntax, such as nested values, is supported.

A comment option is not a comment; it may not contain any text other than JSON syntax. Including non-JSON text within the /* ... */ delimiters results in an SQLCODE -153 error. InterSystems SQL does not validate the contents of the JSON string.

The #OPTIONS keyword must be specified in uppercase letters. No spaces should be used within the curly brace JSON syntax. If the SQL code is enclosed with quote marks, such as a Dynamic SQL statement, quote marks in the JSON syntax should be doubled. For example: myquery="SELECT Name FROM Sample.MyTest /*#OPTIONS {""optName"":""optValue""} */".

You can specify a /*#OPTIONS */ comment option anywhere in SQL code where a comment can be specified. In displayed statement text, the comment options are always shown as comments at the end of the statement text.

You can specify multiple /*#OPTIONS */ comment options in SQL code. They are shown in returned Statement Text in the order specified. If multiple comment options are specified for the same option, the last-specified option value is used.

The following comment options are documented:

  • /*#OPTIONS {"DynamicSQLTypeList":"10,1,11"}

  • /*#OPTIONS {"NoTempFile":1} */

Display

The /*#OPTIONS */ comment options display at the end of the SQL statement text, regardless of where they were specified in the SQL command. Some displayed /*#OPTIONS */ comment options are not specified in the SQL command, but are generated by the compiler pre-processor. For example /*#OPTIONS {"DynamicSQLTypeList": ...} */.

The /*#OPTIONS */ comment options display in the Show Plan Statement Text, in the Cached Query Query Text, and in the SQL Statement Statement Text.

A separate cached query is created for queries that differ only in the /*#OPTIONS */ comment options.

FeedbackOpens in a new tab