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

Compiling and Executing a Query
HP NonStop SQL/MX Query Guide—523728-003
1-6
Improving Query Performance
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, 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 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.
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
•
The computation of the transitive closure of “=” predicates and rewrite of predicate
factors based on it
Note. The SQL compiler has been enhanced to improve the quality of query plans for complex
queries that include multitable joins of up to 12 tables.