PREFETCH

An application can use this directive to request that OpenESQL use block fetches for cursors. This can provide performance benefits, similar to array fetching, without having to change program logic. The performance benefit depends on the value of n and on whether the ODBC driver in use is already configured to use prefetching.

If n is less than 1000, it controls the number of rows to be fetched per batch and the same number of rows is fetched for all cursors. If n is greater than or equal to 1000, it sets the size of the prefetch buffer for each cursor. All cursors will have the same buffer size but the number of rows prefetched will depend on the overall size of the row returned by the query for each cursor.

When PREFETCH=n is used with Microsoft SQL Server, AUTOFETCH is also used for read only cursors. Cursors which are not read only are forced to be keyset cursors and can be used for positioned updates. PREFETCH=n is only supported with DB2, Oracle and Microsoft SQL Server.

Syntax:

PREFETCH=n

Properties:

Default:

PREFETCH=8

Scope:

Used at compile time:	No
Behavior at run time:	Process

See Scope - OpenESQL SQL Compiler Directive Options for more information.

Comment:

Much of the functionality provided by PREFETCH is now incorporated into the functionality of the BEHAVIOR SQL compiler directive option. As a result, PREFETCH is likely to be deprecated in a future release.