Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.0 » SQL Anywhere Server - Programming » Introduction to Programming with SQL Anywhere » Using SQL in applications » Working with cursors


Fetching multiple rows

Multiple-row fetching should not be confused with prefetching rows. Multiple row fetching is performed by the application, while prefetching is transparent to the application, and provides a similar performance gain. Fetching multiple rows at a time can improve performance.

Multiple-row fetches

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.

Using multiple-row fetching
  • 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.