Databases Reference
In-Depth Information
can retrieve data from the server in arrays, further improving the performance by
reducing network traffic.
For ODBC drivers that do not support
SQLExtendedFetch
, your application
can enable forward-only cursors using the ODBC cursor library by calling
SQLSetConnectAttr
. Using the cursor library won't improve performance, but it
also won't decrease application response time when using forward-only cursors
because no logging is required. For scrollable cursors, it's a different story (see
“Using the Cursor Library,” page 141). In addition, using the cursor library when
SQLExtendedFetch
is not supported natively by the driver simplifies code
because the application can always depend on
SQLExtendedFetch
being avail-
able. The application doesn't require two algorithms (one using
SQLExtendedFetch
and one using
SQLFetch
).
ODBC defines two types of cursors:
•
Forward-only
•
Scrollable (static, keyset-driven, dynamic, and mixed)
Scrollable cursors let you go both forward and backward through a result set.
However, because of limited support for server-side scrollable cursors in many
database systems, ODBC drivers often emulate scrollable cursors, storing rows
from a scrollable result set in a cache on the machine where the driver resides
(client or application server).
Unless you are certain that the database natively supports using a scrollable
result set, do not call the
SQLExtendedFetch
function to find out how many rows
the result set contains. For drivers that emulate scrollable cursors, calling
SQLExtendedFetch
causes the driver to retrieve all results across the network to
reach the last row. This emulated model of scrollable cursors provides flexibility
for the developer but comes with a performance penalty until the client cache of
rows is fully populated. Instead of calling
SQLExtendedFetch
to determine the
number of rows, count the rows by iterating through the result set or obtain the
number of rows by submitting a
Select
statement with the
Count
function. For
example:
SELECT COUNT(*) FROM employees
Unfortunately, there's no easy way to tell if a database driver uses native
server-side scrollable cursors or emulates this functionality. For Oracle or
