Forcing Cursors to be Non-Scrolling

Eliminate the temporary store node in queries that return a very large result set to improve performance.

When you use scrolling cursors with no host variable declared, Sybase IQ creates a temporary store node where query results are buffered. This storage is separate from the temporary store buffer cache. The temporary store node enables efficient forward and backward scrolling when your application searches through a result set.

However, if the query returns very large numbers (such as millions) of rows of output, and if your application performs mostly forward-scrolling operations, the memory requirements of the temporary store node may degrade query performance. To improve performance, eliminate the temporary store node by issuing the following command:

SET TEMPORARY OPTION FORCE_NO_SCROLL_CURSORS = ‘ON’

Note: If your application performs frequent backward-scrolling, setting the FORCE_NO_SCROLL_CURSORS option to ON may actually degrade query performance, as the absence of the temporary cache forces Sybase IQ to re-execute the query for each backward scroll.

If your application rarely performs backward-scrolling, make FORCE_NO_SCROLL_CURSORS = ‘ON’ a permanent PUBLIC option. It will use less memory and improve query performance.