Off, Conditional, Always

Conditional

Can be set for an individual connection, or for PUBLIC. You must have the SET ANY PUBLIC OPTION system privilege to set this option for PUBLIC or for another user or role. Takes effect immediately.

This option controls whether rows are fetched to the client side in advance of being made available to the client application. Fetching several rows at a time, even when the client application requests rows one at a time (for example, when looping over the rows of a cursor), can cut down on response time and improve overall throughput by cutting down the number of requests to the database.

The Always value must be used with caution, as it affects some cursor semantics. For example, it causes the sensitive cursor to become asensitive. Old values may be fetched if the value was updated between the prefetch and the application's fetch request. In addition, using prefetch on a cursor that involves a proxy table can cause the error -668, Cursor is restricted to FETCH NEXT operations, if the client attempts to re-fetch prefetch rows. A client may attempt to re-fetch prefetch rows after a rollback or on a fetch relative 0, if a fetch column is re-bound or bound for the first time after the first fetch, or when GET DATA is used.

The value sensitive cursor types include the ESQL SENSITIVE and SCROLL cursor types, and the ODBC and OLE DB DYNAMIC and KEYSET cursor types.

The setting of the prefetch option is ignored by Sybase Open Client and jConnect connections.

If the DisableMultiRowFetch connection parameter is set to YES, the prefetch database option is ignored and no prefetching is done.

This option previously accepted the value On. This value is now an alias for Conditional.

• Prefetches
• DisableMultiRowFetch (DMRF) connection parameter
• prefetch connection property