NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-87
Examples—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.
The following 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 the following:
°
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.
°
Avoid having a process open multiple cursors on a table when any of the cursors
is used to update that table. If this is unavoidable, consider using CONTROL
TABLE SEQUENTIAL READ OFF.
Examples—CONTROL TABLE
•
The following example sets SYNCDEPTH to 0 for the nonaudited table DEPT. You
might use such a directive before selecting and displaying all rows of the table, for
example, but not before a query that changes data.
CONTROL TABLE PERSNL.DEPT SYNCDEPTH 0;
•
The following example requests buffering for sequential INSERT operations on
table CUSTOMER:
CONTROL TABLE SALES.CUSTOMER SEQUENTIAL INSERT ON;