CURSOR_WINDOW_ROWS option

Function

Defines the number of cursor rows to buffer.

Allowed values

20 – 100000

Default

200

Scope

DBA permissions are not required to set this option. Can be set temporary, for an individual connection, or for the PUBLIC group. Takes effect immediately.

Description

When an application opens a cursor, Sybase IQ creates a FIFO (first-in, first-out) buffer to hold the data rows generated by the query. CURSOR_WINDOW_ROWS defines how many rows can be put in the buffer. If the cursor is opened in any mode other than NO SCROLL, Sybase IQ allows for backward scrolling for up to the total number of rows allowed in the buffer before it must restart the query. This is not true for NO SCROLL cursors as they do not allow backward scrolling.

For example, with the default value for this option, the buffer initially holds rows 1 through 200 of the query result set. If you fetch the first 300 rows, the buffer holds rows 101 through 300. You can scroll backward or forward within that buffer with very little overhead cost. If you scroll before row 101, Sybase IQ restarts that query until the desired row is back in the buffer. This can be an expensive operation to perform, so your application should avoid it where possible. An option is to increase the value for CURSOR_WINDOW_ROWS to accommodate a larger possible scrolling area; however, the default setting of 200 is sufficient for most applications.