Databases Reference
In-Depth Information
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, JDBC 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, such as
rs
, do not call the
rs.last()
and
rs.getRow()
methods to
find out how many rows the result set contains. For drivers that emulate scrol-
lable cursors, calling
rs.last()
results in the driver retrieving all results across
the network to reach the last row. This emulated model of scrollable cursors pro-
vides flexibility for the developer but comes with a performance penalty until the
client cache of rows is fully populated. Instead of calling
rs.last()
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
MySQL, you know the driver emulates scrollable cursors, but for other databases,
it's more complicated. See “Using Scrollable Cursors,” page 36, for details about
which common databases support server-side scrollable cursors and how data-
base drivers emulate scrollable cursors.
Performance Tip
In general, do not write code that relies on the number of result rows
from a query because database drivers often must retrieve all rows in a
result set to determine how many rows the query will return.
When designing your database schema, it's obvious that you need to think about
the impact of storage requirements on the database server. Less obvious, but just
as important, you need to think about the network traffic required to move data
