SET FETCHBUFFER (E-SQL)

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™. 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;

See Also