Database Reference
In-Depth Information
Row Prefetching
When an application fetches data from a database, it can do so row by row or, better yet, by fetching numerous rows at
the same time. Fetching numerous rows at a time is called
row prefetching.
How It Works
The concept of row prefetching is straightforward. Every time an application asks the driver to retrieve a row from
the database, several rows are prefetched with it and stored in client-side memory. In this way, several subsequent
requests don't have to execute database calls to fetch data. They can be served from the client-side memory. As a
result, the number of round-trips to the database decreases proportionally to the number of prefetched rows. Hence,
the overhead of retrieving result sets with numerous rows may be strongly reduced. As an example, Figure
15-13
shows you the response time of retrieving 100,000 rows by increasing the number of prefetched rows up to 50. The
Java class in the
RowPrefetchingPerf.java
file was used for this test.
Figure 15-13.
The time needed to retrieve a result set containing numerous rows is strongly dependent on the number of
prefetched rows
It's essential to understand that the poor performance of the retrieval without row prefetching (in other words,
row by row processing) is
not
caused by the database engine. Instead, it's the application that causes it and in turn
suffers from it. This becomes obvious when looking at the execution statistics generated with SQL trace for the
nonprefetching case. The following execution statistics show that even if the client-side elapsed time lasted about 37
seconds (see Figure
15-13
), only 2.3 seconds were spent processing the query on the database side!
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 100001 2.14 2.30 213 100004 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 100003 2.14 2.30 213 100004 0 100000
