SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-32
Enhancing Sort Performance
Enhancing Sort Performance
You can enhance the performance of sorts within SQL queries in several ways:
•
Use subsorts, configured by specifying appropriate SUBSORT attributes in
=_SORT_DEFAULTS DEFINEs.
•
Direct FastSort to use additional memory when sorting data by setting the VLM
attribute ON in a user-specified SORT DEFINE or a =_SORT_DEFAULTS
DEFINE. When ON, the VLM attribute allows FastSort to use up to 127.5
Megabytes of extended memory (if available on the system), enhancing
performance significantly. FastSort uses the additional memory in either of two
ways:
°
To extend the size of the sort tree beyond its default limit of 32 kilobyte nodes if
the extended size would permit the sort to be done in a single pass
°
To store some intermediate sorting runs and thereby reduce disk input-output
to a minimum if the sort is still too large for a single pass
•
Avoid using a busy processor for your sort operations. Instead, specify the
processor attribute in a =_SORT_DEFAULTS DEFINE to direct all main sort and
subsort operations to another processor.
•
Adjust the execution priority of a sort operation by defining the PRI attribute in a
=_SORT_DEFAULTS DEFINE to alter the priority.
•
Avoid excessive contention for your sort scratch file by using the SCRATCH
attribute in a =_SORT_DEFAULTS DEFINE to redirect scratch operations to
another volume. In particular, the primary partition's volume, which is the default
volume for the scratch file, often becomes very busy; if your scratch file resides on
this volume, you might want to move it to another. For cases in which performance
is vitally important, you might dedicate an entire volume to sort scratch operations.
•
Use SCRATCH, SCRATCHON, AND NOSCRATCHON in a SORT DEFINE so that
FastSort can build a pool of scratch files on appropriate volumes. For more
information, see the FastSort Manual.
For more information about invoking FastSort from SQL, see the FastSort Manual.
Understanding Concurrency
Concurrency is access to the same data by two or more processes at the same time.
The degree of concurrency available depends on the purpose of the access, the
access mode, and whether virtual sequential block buffering (VSBB) is used for the
access.
NonStop SQL/MP provides concurrent database access for most operations, but some
longer-running DDL and utility operations can reduce concurrent access.