SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Compiling and Executing a Query
HP NonStop SQL/MX Query Guide—523728-003
1-7
Improving Query Performance
•
Canonical reorder of the tables in the query tree
•
Constant folding. Constant folding is a compiler optimization technique where an
expression, consisting only of constants, is evaluated at compile time. For
example:
WHERE AGE_IN_DAYS>(2005-1960)*365
When constant folded, this expression becomes WHERE AGE_IN_DAYS>16425.
•
Syntactic and semantic sort elimination
In the optimizer, transformations are conditional and performed based on cost. They
are also applied by rules. See Rule-Based Solutions on page 1-4.
A range of options exists for improving query performance. Some involve simple
changes to the system defaults and others are more complex and involve investigating
the query plan and making appropriate changes to the query or query plan.
Influencing Query Performance
You can influence query performance in online transaction processing environments
through one or more of these methods:
•
Augment or modify the database by adding indexes.
•
Refine table statistics by adding statistics for all columns used in the query. Ensure
that statistics have been generated for all columns used in the query. For more
information, see Section 3, Keeping Statistics Current.
•
Use FIRST 1 syntax for nonunique SELECT ... INTO queries:
SELECT [FIRST 1] a INTO :hv ...
•
Use read-only SELECT statements whenever possible:
CONTROL QUERY DEFAULT readonly_cursor 'TRUE'
To ensure that the MXCMP costing model works correctly, first perform a FUP
RELOAD and then UPDATE STATISTICS on tables and indexes. For more information
about using FUP RELOAD, see the SQL/MX Installation and Management Guide. For
more information about using the UPDATE STATISTICS statement, see the SQL/MX
Reference Manual.
•
Force plans through the CONTROL QUERY SHAPE utility or through CONTROL
QUERY DEFAULT JOIN_ORDER_BY_USER. For more information, see
Section 5, Forcing Execution Plans.
•
If possible, use host variable types that match the database column type. In all
three of these examples, col is the exact same data type as hvar:
°
WHERE predicate match:
SELECT a FROM t WHERE col = :hvar