SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-27
Comparison of Buffering Types
into the table, a high percentage of lock waits and timeouts might occur. You can
disable VSBB for insert operations by specifying this directive:
CONTROL TABLE * SEQUENTIAL INSERT OFF
Comparison of Buffering Types
This discussion compares the three types of buffering techniques, using the example
described earlier in this subsection.
Table 4-1 shows the number of messages transferred between the disk process and
the file system, the number of rows and the number of bytes transferred from the disk
process to the file system, and the number of bytes read from disk by the disk process
for each type of buffering mode: single row, RSBB, and VSBB.
As shown in this table, the choice of buffering mode can have a dramatic influence on
the number of messages required for a query and can, therefore, influence
performance in a major way.
For online transaction processing, you might find that single-row access gives the
fastest response time, because the disk process responds immediately upon finding
the first qualifying row. Alternately, with VSBB, the response might be delayed while
the disk process continues scanning to fill the virtual block. To disable SBB, use the
SEQUENTIAL READ OFF option of the CONTROL TABLE statement. (For more
information, see the CONTROL TABLE SEQUENTIAL READ statement in the SQL/MP
Reference Manual).
Note. Disabling sequential insert or update operations does not automatically disable
sequential read operations. You must specify CONTROL TABLE SEQUENTIAL READ OFF to
disable sequential read operations.
Table 4-1. A Comparison of Buffering Modes
Buffering
Mode
Number of
Messages
Between File
System and
Disk Process
Number of
Bytes Read
From Disk by
Disk Process
Number of
Bytes
Transferred to
File System for
Each Message
Number of
Rows
Transferred to
File System
Single Row 90 48K 2.2K 90
RSBB 12 48K 48.0K 100
VSBB 1 48K 2.2K 90
Note. If stable access is used, VSBB requires 3 messages, because a maximum of 32 rows
can be kept locked within any virtual sequential block.