SET FETCHBUFFER
The SET FETCHBUFFER statement sets internally the number of rows to be retrieved at one time for standard cursors.
Syntax
SET FETCHBUFFER num_rows
Arguments
num_rows
Is the number of rows to be retrieved at one time from Microsoft® SQL Server™ 2000. Embedded SQL for C buffers num_rows internally, and it returns one row to the application for each FETCH statement issued.
Remarks
The default is 1 if SET ANSI_DEFAULTS is ON. Otherwise, the default is 10. After this statement is issued, it affects all subsequent cursor OPEN statements. Because a FETCH statement returns only a single row, the SET FETCHBUFFER statement is for performance tuning only.
The number of fetch operations required to retrieve all data from a cursor can be reduced by setting the num_rows parameter higher than the default setting of 10. You should ensure that the client has enough available memory for you to increase this setting. You might need to reduce the default setting of the num_rows parameter if client memory is constrained, or if the cursors have many columns or wide columns.
Examples
EXEC SQL SET FETCHBUFFER 5;