SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-91
Considerations—CONTROL TABLE

The optimizer often chooses virtual sequential block buffering (VSBB) when
compiling a cursor definition. (You can determine whether SQL used VSBB in a
specific case by looking at the EXPLAIN output for the cursor.)
During a cursor session, conflicting DML operations can invalidate the cursor's
buffering for the table. Each invalidation forces the next FETCH operation to send
a message to the disk process to retrieve a new buffer; this can degrade
performance substantially.
These operations invalidate the buffer for cursor operations:

Any INSERT on the same table by the current process

A stand-alone UPDATE or DELETE on the same table (directly or through a
protection view) from within the same process

An UPDATE WHERE CURRENT or DELETE WHERE CURRENT using a
different cursor to access the same table (directly or through a view) from
within the same process
For example, a loop containing both a FETCH statement and a stand-alone
UPDATE or DELETE statement on the same table would invalidate the cursor's
buffer on every loop iteration. You can change your program logic to minimize
or eliminate the performance penalty because of conflicts by doing:

Avoid INSERT operations within a cursor session.

Use UPDATE WHERE CURRENT or DELETE WHERE CURRENT operations
against the current cursor rather than stand-alone UPDATE or DELETE
operations.
A table has more than one cursor defined by the
same process, or an alternate access path is
selected.
XN.A.
SEQUENTIAL READ OFF is in effect for the
table.
XN.A.
SEQUENTIAL UPDATE OFF is in effect for the
table.
XN.A.
SEQUENTIAL INSERT OFF is in effect for the
table.
N.A. X
An INSERT or UPDATE operation is mixed with
other operations instead of occurring in
sequence.
XX
The INSERT or UPDATE operation is not in
sequential order.
XX
Condition
Prevents
Buffering
for UPDATES
Prevents
Buffering
for INSERTS