Row Pre-fetch
For definitions of the technical terms used in this chapter, see Terminology.
Under certain circumstances, upon execution of a SELECT statement, this release of the database engine attempts to pre-fetch to the client the first two rows of the resulting rowset. This pre-fetch greatly improves the performance of fetching data from result sets that consist of zero or one row.
Pre-fetching rows can be a costly waste of time if the result set consists of more than one row and the first data retrieval operation requests a row other than the first row in the result set, such as the last row. Therefore, pre-fetching is limited to a maximum of two rows with the goal of improving performance for the cases that would benefit most while avoiding cases where pre-fetching would not provide significant benefits.
Pre-fetching occurs only if Array Fetch is enabled in the advanced connection attributes for client DSNs (see Advanced Connection Attributes for Client DSN). Array fetching is similar to pre-fetching except that an array fetch does not occur until the first explicit data retrieval operation is performed. This difference exists because the first explicit data retrieval operation may provide enough information to allow the database engine to extrapolate how the rest of the result set will be retrieved. For example, if the first data retrieval operation is a call to the ODBC API SQLFetch, then the database engine can assume with complete certainty that the entire result set will be retrieved one record at a time in the forward direction only. This assumption can be made because, according to the ODBC specification, an SQLFetch entails that the rest of the result set will be retrieved via SQLFetch as well. On the other hand, if a SQLExtendedFetch call is made, and the row set size is greater than one, then the client assumes that the developer-specified rowset size is optimal, and it does not override that setting with the array fetch.
Pre-fetching occurs only when all of the following conditions are satisfied:
•
Array fetch is enabled.
•
The result set does not include large variable length data; for example, the selection list does not contain a column of type LONGVARCHAR or LONGVARBINARY.
•
The result set does not include bookmarks.
For example, pre-fetching does not occur if the ODBC API SQLSetStmtOption is called prior to generating the result set, specifying the SQL_USE_BOOKMARKS option and the SQL_UB_ON value.
•
A cursor with read-only concurrency is being used.
For example, pre-fetching does not occur if the ODBC API SQLSetStmtOption is called, specifying the SQL_CONCURRENCY option and any value other than SQL_CONCUR_READ_ONLY, prior to generating the result set. By default, concurrency is read-only.