SQL/MX 2.x Database and Application Migration Guide (G06.23+, H06.04+, J06.03+)

Converting SQL/MP Applications to SQL/MX
Applications
HP NonStop SQL/MX Database and Application Migration Guide—540435-005
10-59
Optimizing Query Execution Performance
Optimizing Query Execution Performance
To ensure optimal performance of SQL/MX applications, you must understand how to
influence and optimize query execution plans in NonStop SQL/MX.
Setting System Defaults
Certain system defaults influence query execution plans. In NonStop SQL/MP, you
specify the default settings by using CONTROL directives. In NonStop SQL/MX, you
can specify the default settings by inserting them into the SYSTEM_DEFAULTS table.
Setting a default in the SYSTEM_DEFAULTS table changes the default for all users
and applications on the system. You can also override system default settings by using
the SQL/MX CONTROL QUERY DEFAULT and CONTROL TABLE statements. For
descriptions of the default attributes that are supported in the SYSTEM_DEFAULTS
table, see the
SQL/MX Reference Manual. To review and set system defaults for a
particular SQL/MX release, see the
SQL/MX Installation and Management Guide.
Updating Table Statistics
The SQL/MX UPDATE STATISTICS command updates histogram statistics for one or
more groups of columns in an SQL/MP or SQL/MX table. The SQL/MX compiler uses
the histogram statistics to devise optimized access plans and determine selectivity of
predicates, indexes, and tables. Because selectivity directly influences the cost of
access plans, regular collection of statistics increases the likelihood that NonStop
SQL/MX chooses efficient access plans. If the statistics are incorrect, the SQL/MX
compiler might not select the best access plan for performance.
Update statistics when you first create and populate a database and whenever the
database changes significantly, such as when its data or definition changes (added
columns, new indexes). Update statistics for every table column involved in a query.
For more information about UPDATE STATISTICS, see the
SQL/MX Reference
Manual and the SQL/MX Query Guide.
Displaying and Analyzing Query Execution Plans
In NonStop SQL/MX, you can display and analyze query execution plans by using the
EXPLAIN function, which is similar to the SQL/MP EXPLAIN directive, or by using the
DISPLAY_EXPLAIN command or the Visual Query Planner graphical user interface.
For more information about using these methods, see the
SQL/MX Query Guide.
Forcing Query Execution Plans
The SQL/MX optimizer usually selects the best query execution plan. However,
sometimes you might want to change the plan that the optimizer generates. To do so,
you must force the plan, taking control away from the optimizer. Unlike NonStop
SQL/MP, which allows you to use the CONTROL TABLE directive to force query