Specify Optimization Hints in Queries

The InterSystems SQL query optimizer uses sophisticated and flexible algorithms to optimize the performance of complex queries. It incorporates detailed knowledge of table statistics, parallelization strategies, and schema definitions. In the vast majority of cases, the optimizer produces the optimal query plan and delivers the best possible performance without any use intervention.

However, in some situations, such as when investigation unexpected performance behavior, you may with to influence specific aspects of query execution. InterSystems SQL provides query hints for this purpose. These hints should be used selectively and primarily as diagnostic tools.

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.

Standard Diagnostic Hints

SELECT statements can make use of diagnostic hint keywords to assist with diagnosing query performance issues. These keywords selectively enable or disable specific components of query processing, allowing you to isolate the impact of these components. You may also specify multiple keywords, separated by blank spaces, but it is typically more useful to test one component at a time. These hints are supplied in the FROM clause, unless otherwise noted.

The following hints are safe for exploratory use and can help identify the root cause of suboptimal performance:

%DORUNTIME and %NORUNTIME: Enable or disable Runtime Plan Choice for use in your query. Unlike the other keywords, these options must be specified in the SELECT statement, not the FROM clause.
%FIRSTTABLE: Identifies a table, specified later in the JOIN sequence, that the query optimizer should use to begin the join sequence. Unlike other keywords, this %FIRSTTABLE takes an argument, tablename. See below for an example of its usage in a FROM clause:
```
FROM %FIRSTTABLE P Sample.Employee AS E 
  JOIN Sample.Person AS P ON E.Name = P.Name
```
%INORDER: Force the query optimizer to perform JOIN operations in the order that the tables are listed in the FROM clause.
%NOFIXEDSTATS: Ignore fixed table statistics, allowing the optimizer to rely on collected table statistics instead.
%NOFPLAN: Disable the use of frozen plans, ensuring the optimizer generates a new plan.
%NOINDEX: Prevent the query optimizer from using an index on a specific condition in a WHERE clause. For example, WHERE %NOINDEX Age >= 18.
%PARALLEL and %NOPARALLEL: These keywords enable and disable, respectively, parallel execution.

Advanced Diagnostic Hints

Other query hints influence internal optimizer behavior and other low-level components. These hints are intended for advanced tuning scenarios that arise when the optimizer may not be selecting the best plan automatically and should never be used without guidance from the InterSystems Worldwide Response Center (WRC). Such hints include (but are not limited to): %ALLINDEX, %FIRSTTABLE, %NOFLATTEN, and %NOTOPOPT.

If the standard diagnostic hint keywords do not help you pinpoint what may be causing suboptimal query performance, contact the WRC, which can help discover the root cause of your issues and may direct you to use these advanced diagnostic hints.