SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-21
Reducing Messages With Buffering Options
Reducing Messages With Buffering Options
This subsection describes how SQL retrieves data from the disk process. Retrieval can
be by row or by block. The size of a block is specified for a table when the table is
created.
Types of Buffering
The optimizer can choose between these types of access:
•
Single row—SQL returns data from the disk process one row at a time.
•
Sequential block buffering (SBB):
°
Real Sequential Block Buffering (RSBB)—SQL obtains data from the disk
process one block at time. Data blocks sent to the requesting process are
exact copies of what is on the disk.
°
Virtual Sequential Block Buffering (VSBB)—the disk process builds a block in
the disk process that has only the data the requester is interested in. Thus, the
disk process performs column selection (projection) and row selection
(restriction). The data can come from one or more table blocks. The data area
used for this is called a virtual block because it is not an exact copy of data on
disk.
The optimizer attempts to minimize the number of messages and the amount of data
transferred between the file system and the disk process. Using SBB is one way to
achieve this goal.
When an application uses SBB (either RSBB or VSBB), SQL retrieves a whole block of
rows at a time. All rows in the block are locked. When the application process requests
the next row, the file system returns the next row from the copy of the physical block of
rows. Therefore, SBB reduces the number of messages between the file system and
the disk process by the file's physical blocking factor (the number of rows per block).
If SBB is used, SQL chooses the type of SBB (virtual or real) to use. The optimizer
does not, however, always choose SBB, because SBB involves a certain amount of
overhead.
The following subsections describe each buffering mode in more detail, using an
INVNTRY table defined with these columns:
ITEM_NAME (20 bytes)
RETAIL_PRICE (4 bytes)
ITEM_ON_HAND (4 bytes)
COMMENTS (400 bytes)