Java Programming with Oracle SQLJ

10.1. Row Prefetching

SQL queries sometimes return large numbers of rows. A SQLJ program needs to communicate with the database in order to retrieve those rows. Each communication between a SQLJ program and the database is known as a round trip, and each round trip exacts a cost in terms of time. You can increase performance by reducing the number of round trips required to handle the rows returned by a query. By default, a SQLJ program receives a maximum of 10 rows at a time from the database in each round trip; this is known as prefetching the rows. As you will soon see, the number of rows fetched during each round trip can be changed, allowing a SQLJ program to receive many more rows during each round trip to the database. This can increase performance dramatically when retrieving many rows from a database over a network.

For example, assume that you have a query that returns 10,000 rows from a database running on a remote machine, and that your program receives 10 rows at a time during each round trip. This scenario results in 1,000 round trips across the network. If you modify your program to receive 100 rows during each round trip, the result is only 100 round trips. The fewer the round trips across the network, the faster all the rows are received by the program.

10.1.1. Specifying the Number of Rows to Prefetch

Prefetching is set up for the underlying JDBC connection in a SQLJ connection context. (Connection contexts are described in Chapter 8.) The method called