SQL/MX 3.x Query Guide (H06.22+, J06.11+)

Compiling and Executing a Query
HP NonStop SQL/MX Query Guide—640323-001
1-19
Factors That Can Affect Compile Time
Factors That Can Affect Compile Time
•
The number of tables joined, which is the most important factor in determining
query complexity
•
Other query complexity factors, such as the number of group bys, unions, and
subqueries
•
The number of predicates on the query and the number of columns in the predicate
•
The presence of indexes, which can increase complexity as the new access path
must be considered
•
Whether statistics have been updated and the number of intervals in the
histograms
•
Default settings (see Section 4, Reviewing Query Execution Plans), especially the
optimization controls such as OPTIMIZATION_LEVEL
Remember that the number of joins is not the only factor that can affect compile time.
For example, a 4-way natural join on a 200-column table can take considerably more
time than an 8-way join between tables with only a few columns.
How the Executor Processes the Plan
NonStop SQL/MX uses a data-flow and scheduler-driven task model to execute
queries. After a query is optimized, the optimizer generates an optimized, executable
query plan that goes to the executor. The executor prepares a node for each operator.
Each operator is an independent task and data flows between operators through in-
memory queues (up and down) or by interprocess communication. The queue pair
operates between two operators. Queues between tasks allow operators to exchange
multiple requests or result rows at a time. A scheduler coordinates the execution of
tasks and runs whenever it has data on one of its input queues.
Join
Group byScan
Scan
Tasks
Queues
VST014.vsd