SQL/MX 3.2.1 Query Guide (H06.26+, J06.15+)

Compiling and Executing a Query
HP NonStop SQL/MX Release 3.2.1 Query Guide—663851-003
1-6
Improving Query Performance
This feature improves the performance of the compiler when the plan can be produced
from cache rather than through a full compilation. The queries that can be considered
for plan caching include simple TP-style inserts, updates, deletes, selects, and joins.
Two queries are considered equivalent for the purposes of caching if their canonical
forms are the same. For query caching, the canonical form of a query is constructed
by:

Removing unmeaningful white space differences

Removing unmeaningful case differences

Expanding '*' notation in select lists

Resolving all object names to fully qualified names

Replacing most constant literals with parameters

Encoding all CONTROL QUERY DEFAULT and CONTROL TABLE statements that
have been previously executed in the current SQL/MX compiler session
When query caching is enabled, NonStop SQL/MX caches the compiled plans of
cacheable statements. When an equivalent query is resubmitted, most of the SQL
compilation is skipped, and the query plan is produced from the cache. Use query plan
caching statistics to determine important information about the caching process in
addition to the current state of stored plans.
Section 6, Query Plan Caching describes the types of cacheable queries, query plan
caching statistics, and the default settings that affect query caching.
Improving Query Performance
The SQL/MX compiler performs a series of internal transformations when generating
the most suitable plan for a query. Some of these transformations are described next.
The actions you can take to influence query performance are described under
Influencing Query Performance on page 1-7.
Query Processing by the Compiler
The query tree obtained by parsing the query goes through numerous transformations.
While the binder and codegen perform relatively simple changes, more complex
transformations are performed in the normalizer and the optimizer stages.
In the normalizer, unconditional transformations are applied to make the query tree
more suitable for subsequent optimization. The major transformations are:

Unconditional predicate transformations

Subquery transformation to joins

Predicates pushed down as far as possible
Note. The SQL/MX compiler has been enhanced to improve the quality of query plans for
complex queries that include multitable joins of up to 12 tables.
TP663851.fm Page 6 Wednesday, January 30, 2013 5:37 PM