SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-31
Controlling the Creation of NonStop SQL/MP
Processes
If you choose a DENSE algorithm, the executor starts with 1 and increments the value
sequentially to obtain the next values for column A.
However, if the values for column A are 25, 135, 400, and 525, then DENSE would not
be a good algorithm. Too many accesses would be made if each value were
sequentially incremented until the next value were found. If you specified a SPARSE
algorithm, the executor would process all qualifying rows for A = 25 and then do an
extra position to find the next value of A that is greater than 25.
If statistics cause the optimizer to make a mistake and choose DENSE instead of
SPARSE, or SPARSE instead of DENSE, you can change the choice by specifying the
correct option in the CONTROL TABLE directive. However, because the executor does
an adaptive DENSE or SPARSE, it switches accordingly when it finds that the chosen
algorithm is not efficient for the column it is accessing.
If you use a CONTROL TABLE directive to force the use of DENSE, the optimizer still
uses SPARSE for character and float data types. If you specify the SYSTEM option,
the system chooses the algorithm for each column.
When the optimizer chooses a SPARSE algorithm, the executor executes only the
SPARSE algorithm. When the optimizer chooses a DENSE algorithm, the executor
uses a SPARSE algorithm to retrieve the first existing key value but then switches to
DENSE. It continues to use DENSE until it makes several misses and then switches
back to SPARSE to get the next existing key value.
For more information on MDAM, see Transformation of Predicates on page 3-4.
Controlling the Creation of NonStop SQL/MP
Processes
The SQL catalog manager and SQL compiler are always started upon demand for their
services. If these processes are not already running when individual SQL statements
are submitted, a longer response time for statements results. Furthermore, after the
processes are started, they normally remain active for about five minutes. Therefore,
you can improve the performance of certain operations if you group the operations
within your program to take advantage of this timing. For example, if a program needs
to create three temporary tables, you could group the three CREATE TABLE
statements together rather than including them at the separate points where the
program might naturally need them. Beyond these techniques, you have no other
control over the life cycle of the SQL processes.