SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-13
Requesting Parallel Processing
Requesting Parallel Processing
SQL can take advantage of multiprocessor architecture by dividing an SQL query into
smaller tasks and assigning the tasks to separate processors. During parallel
processing, each part or partition of data is processed in parallel. After all partitions are
processed, they are merged to produce the final result.
Because databases can span multiple disks, using separate processes to access
these disks can dramatically improve the performance of a query. This approach can
improve the response time of all the basic SQL operations, including selects, inserts,
updates, deletes, joins, and aggregate functions. Parallel execution is especially
helpful when a large number of rows need to be processed by the executor, but only a
small number of rows need to be returned to satisfy the query.
Using the CONTROL EXECUTOR Directive
To request parallel processing, use the CONTROL EXECUTOR directive. The
optimizer then evaluates whether the system can process the entire statement or parts
of the statement in parallel. Specifying parallel execution, however, does not guarantee
that queries are executed in parallel. The optimizer selects the parallel execution plan
only if it is the best plan. In particular, parallel execution is not chosen in these cases:
•
The statement includes a UNION operation.
•
The SELECT statement references more than one table and contains a
noncorrelated, nonquantified subquery.
•
The statement includes a FOR UPDATE OF clause.
•
The CONTROL EXECUTOR PARALLEL EXECUTION OFF statement precedes
the query in the source program file text or in the SQLCI session. (OFF is the
default.)
•
The local system has only one processor or none of the tables in the FROM clause
are partitioned.
•
The system includes fewer than two logical volumes (excluding $SYSTEM).
•
The SELECT statement references a single table that is not partitioned.
•
The SELECT statement references a nonaudited table and the FOR BROWSE
ACCESS option is not specified.
•
A CREATE INDEX statement includes the WITH SHARED ACCESS option.
•
The SQL compiler estimates that the cost is higher to process the query in parallel.
Queries against small tables are usually not candidates for parallel processing. Neither
are queries where relatively few rows are processed by the executor. While these
queries might not be precluded from parallel processing, the cost factor might direct
the optimizer to choose a plan that does not include parallel processing.