ASA Programming Guide
Using SQL in Applications
Adaptive Server Anywhere cursors
Cursor sensitivity and performance
Prefetches and multiple-row fetches are different. Prefetches can be carried out without explicit instructions from the client application. Prefetching retrieves rows from the server into a buffer on the client side, but does not make those rows available to the client application until the application fetches the appropriate row.
By default, the Adaptive Server Anywhere client library prefetches multiple rows whenever an application fetches a single row. The Adaptive Server Anywhere client library stores the additional rows in a buffer.
Prefetching assists performance by cutting down on client/server traffic, and increases throughput by making many rows available without a separate request to the server for each row or block of rows.
For more information on controlling prefetches, see PREFETCH option [database].
The PREFETCH option controls whether or not prefetching occurs. You can set the PREFETCH option to ON or OFF for a single connection. By default, it is set to ON.
In embedded SQL, you can control prefetching on a per-cursor basis when you open a cursor on an individual FETCH operation using the BLOCK clause.
The application can specify a maximum number of rows contained in a single fetch from the server by specifying the BLOCK clause. For example, if you are fetching and displaying 5 rows at a time, you could use BLOCK 5. Specifying BLOCK 0 fetches 1 record at a time and also causes a FETCH RELATIVE 0 to always fetch the row from the server again.
Although you can also turn off prefetch by setting a connection parameter on the application, it is more efficient to set BLOCK=0 than to set the PREFETCH option to OFF.
For more information, see PREFETCH option [database]
In Open Client, you can control prefetching behavior using ct_cursor with CS_CURSOR_ROWS after the cursor is declared, but before it is opened.