This section discusses how fetching multiple rows at a time can improve performance.
Multiple-row fetching should not be confused with prefetching rows, which is described in the next section. Multiple row fetching is performed by the application, while prefetching is transparent to the application, and provides a similar performance gain.
Some interfaces provide methods for fetching more than one row at a time into the next several fields in an array. Generally, the fewer separate fetch operations you execute, the fewer individual requests the server must respond to, and the better the performance. A modified FETCH statement that retrieves multiple rows is also sometimes called a wide fetch. Cursors that use multiple-row fetches are sometimes called block cursors or fat cursors.
In ODBC, you can set the number of rows that will be returned on each call to SQLFetchScroll or SQLExtendedFetch by setting the SQL_ATTR_ROW_ARRAY_SIZE or SQL_ROWSET_SIZE attribute.
In embedded SQL, the FETCH statement uses an ARRAY clause to control the number of rows fetched at a time.
Open Client and JDBC do not support multi-row fetches. They do use prefetching.