SQL/MX Comparison Guide for SQL/MP Users

Optimizer and Executor
HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003
5-4
SQL/MP Iterator Model
This table highlights the differences between the SQL/MP executor and the SQL/MX
executor.
SQL/MP Iterator Model
The SQL/MP executor uses an iterator model, also called a control flow model. In the
iterator model, a query plan is executed by a series of function calls. Each function
implements an operator in the query plan. Because the implementation is through a
series of function calls, no pipelining or parallel processing is possible.
The interface between the SQL/MP executor and DAM is synchronous. The executor
process is blocked while it waits for a reply from a DAM. NonStop SQL/MP uses ESPs
to keep multiple DAMs active at the same time. NonStop SQL/MP requires a 1:1
mapping between ESPs and the DAMs of a partitioned table.
The next figure shows an example of the iterator model. This iterator model is a
serialized type of model, meaning that only one function is active at a time. The
numbers in the figure relate to these operations:
1. The join calls the scan function.
2. The scan asks DAM to retrieve rows.
3. Rows are returned from DAM to the scan.
4. Rows are returned from the scan function call to the join.
5. The join calls the scan function, providing as input the row produced by Step 4.
6. The scan asks DAM to retrieve all rows matching the input.
7. Rows are returned from DAM to the scan.
8. Rows are returned from the scan function to the join.
SQL/MP Executor SQL/MX Executor
Control flow iterator model. See SQL/MP
Iterator Model.
Data-flow, scheduler-driven task model. See
SQL/MX Task Model.
Nested join, hash join, and merge join
supported.
Nested join, hash join, and merge join
supported. Within a join, also supports range
partitioning.
Aggregate and sort GROUP BY queries
can be executed in DAM.
Aggregate, sort and hash GROUP BY queries
can be executed in DAM.
Parallel join, GROUP BY, sort operations
supported through ESPs.
Increased parallelism supported through no-
wait interface to DAM (no ESPs needed for
some parallel plans).
Result data from DAM and inserted rows
can be buffered in larger messages
(VSBB, RSBB, and Insert VSBB).
Request and result data to and from DAM and
inserted rows are always buffered.
Uses ESPs to keep multiple DAM
processes active at the same time.
Requires a 1:1 mapping between ESPs
and DAMs of a partitioned table.
Does not require 1:1 mapping between ESPs
and DAMs of a partitioned table.