SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-28
Requesting Buffering
Requesting Buffering
You can use the CONTROL TABLE directive to set buffering to ON, OFF, or ENABLE
for sequential insert, read, or update operations. The ENABLE setting lets the system
choose whether to use SBB or not for a specific query; this is the default for read
operations.
This example requests buffering for sequential UPDATE operations:
CONTROL TABLE SALES.CUSTOMER SEQUENTIAL UPDATE ON
The preceding directive requests buffering of update operations by the file system for
the disk process.
In a host language program, specific placement rules might apply to the
CONTROL TABLE directive. For more information, see the SQL/MP Programming
Manual for your host language.
Optimizing Sequential Access With Block Buffering
When a row is inserted into an SQL table or view, each INSERT statement results in a
single message to the disk process. For files in which inserts are random throughout
the file, using the INSERT statement in this way is the best method. When inserts are
performed in key groups of sequential nature, however, as in batch operations, it is
more efficient to block the inserts, grouping sequential keys in one message to the disk
process.
You can use the CONTROL TABLE statement to control insert operations to allow for
sequential block buffering or to force single writes. For audited tables, the default
option for CONTROL TABLE SEQUENTIAL is ENABLE, which enables the system to
decide which method (sequential block buffering or single messages) is the most
efficient for these operations. For nonaudited tables, the default option for CONTROL
TABLE SEQUENTIAL is OFF, indicating the single messages method.
You can control the option programmatically so that batch throughput performance can
be increased. For more information, see the SQL/MP Programming Manual for your
host language.
The CONTROL TABLE SEQUENTIAL ENABLE option is available for update
operations as well as for the insert function.
Effects of Cursor Operations on Performance
The optimizer often chooses VSBB when compiling a cursor definition.
You should be aware, however, that certain operations invalidate buffering for cursor
operations, and performance can be degraded. For more information, see the SQL/MP
Programming Manual for your programming language.