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 setDefaultRowPrefetch( ), defined in the class oracle.jdbc.driver.OracleConnection, accepts an integer value that specifies the number of rows to prefetch during each round trip. For example, the following statement sets the number of rows to prefetch to 20 for the connection context named my_conn_context. The getConnection( ) method is invoked to return the underlying JDBC connection object, which is then cast to the OracleConnection class. SQLJ connections are subclasses of OracleConnection.
((OracleConnection) my_conn_context.getConnection( )).setDefaultRowPrefetch(20);
Let's break this statement down and analyze each part in detail:
- getConnection( )
-
Returns the underlying JDBC connection for the specified SQLJ connection context.
- (OracleConnection)
-
Casts the JDBC connection to the class OracleConnection, which contains the setDefaultRowPrefetch( ) method.
- setDefaultRowPrefetch(20)
-
Specifies that 20 rows should be prefetched in each round trip.
You can also set up prefetching for the underlying JDBC connection in the default connection context. The following statement specifies that 50 rows be prefetched for the default connection context:
((OracleConnection) DefaultContext.getDefaultContext().getConnection( )). setDefaultRowPrefetch(50);
The pattern for this example is very similar to that used in the previous one. The difference is that you have to use DefaultContext.getDefaultContext( ) to return the default context. From that, you can get the connection, which allows you to set the prefetch value.
| In SQLJ Version 8.1.7 and above, setDefaultRowPrefetch( ) is superseded by setFetchSize( ), which is part of the JDBC 2.0 and SQLJ ISO standards. |
|
10.1.2 Checking the Prefetch Count
Just as it's possible to set the prefetch count for a connection, you can also retrieve it. The method getDefaultRowPrefetch( ) returns the current number of rows to be prefetched for a given connection. The following example gets the number of rows that are currently prefetched for the underlying JDBC connection in the connection context my_conn_context:
int prefetch_rows = (OracleConnection) my_conn_context.getConnection()).getDefaultRowPrefetch( );
Similarly, the following example calls getDefaultRowPrefetch( ) for the underlying JDBC connection in the default connection context:
int prefetch_rows = ((OracleConnection) DefaultContext.getDefaultContext().getConnection( )). getDefaultRowPrefetch( );
| In SQLJ Version 8.1.7 and above, getDefaultRowPrefetch( ) is superseded by getFetchSize( ), which is part of the JDBC 2.0 and SQLJ ISO standards. |
|
10.1.3 Example Program: PerformanceExample1.sqlj
This section contains a complete program, called PerformanceExample1.sqlj (Example 10-1), that illustrates how to increase the number of rows prefetched. The program also shows how this can lead to an increase in performance. It performs the following steps:
-
Connects over the network to a database running on another machine. You may need to change the name of the machine specified in the connect( ) method to get the program to run in your environment.
-
Creates a table named prefetch_table. This table stores a single integer column named value.
-
Adds 20,000 rows to prefetch_table.
-
Displays the current value for the default number of rows to prefetch using the getDefaultRowPrefetch( ) method. By default, this value is 10.
-
Calls the retrieveRows( ) method, which is defined in the program. This method uses an iterator to retrieve all the rows from prefetch_table. When all rows are retrieved, the method then displays the elapsed time in milliseconds that was required to select the rows. The retrieveRows( ) method is called three times to give you an idea of the average time taken.
-
Sets the number of rows to prefetch to 100 using the setDefaultRowPrefetch( ) method.
-
Calls the retrieveRows( ) method three more times. Because 100 rows are now retrieved during each round trip, there should be a reduction in the time taken to select the rows.
-
Drops prefetch_table.
Example 10-1. PerformanceExample1.sqlj
/* The program PerformanceExample1.sqlj illustrates how to use row prefetching to improve performance of SQL queries. */ import java.sql.*; import oracle.sqlj.runtime.Oracle; import sqlj.runtime.ref.DefaultContext; import oracle.jdbc.driver.OracleConnection; public class PerformanceExample1 { public static final int ROWS = 20000; public static final int PREFETCH_ROWS = 100; // declare a named iterator class #sql private static iterator PrefetchIteratorClass ( int value ); public static void main(String [] args) { try { // connect to a database on a remote machine Oracle.connect( "jdbc:oracle:thin:@remotehost:1521:orcl", "fundamental_user", "fundamental_password" ); // create a temporary table #sql { CREATE TABLE prefetch_table ( value INTEGER ) }; // add rows to the table for (int count = 0; count < ROWS; count++) { #sql { INSERT INTO prefetch_table (value) VALUES (:count) }; } // display the current number of rows to prefetch int prefetch_rows = ((OracleConnection) DefaultContext.getDefaultContext().getConnection( )). getDefaultRowPrefetch( ); System.out.println("Number of rows to be prefetched = " + prefetch_rows); retrieveRows( ); retrieveRows( ); retrieveRows( ); // set the number of rows to prefetch to PREFETCH_ROWS ((OracleConnection) DefaultContext.getDefaultContext().getConnection( )). setDefaultRowPrefetch(PREFETCH_ROWS); // display the current number of rows to prefetch prefetch_rows = ((OracleConnection) DefaultContext.getDefaultContext().getConnection( )). getDefaultRowPrefetch( ); System.out.println("Number of rows to be prefetched = " + prefetch_rows); retrieveRows( ); retrieveRows( ); retrieveRows( ); // drop prefetch_table #sql { DROP TABLE prefetch_table }; Oracle.close( ); } catch (SQLException e) { System.err.println("SQLException " + e); System.exit(1); } } // end of main( ) private static void retrieveRows( ) throws SQLException { // declare a SQLJ iterator PrefetchIteratorClass prefetch_iterator; long start_time = System.currentTimeMillis( ); // populate the iterator #sql prefetch_iterator = { SELECT value FROM prefetch_table }; long end_time = System.currentTimeMillis( ); System.out.println("Total time for retrieving " + ROWS + " rows was " + (end_time - start_time) + " milliseconds."); } // end of retrieveRows( ) }
The output from this program is as follows:
Number of rows to be prefetched = 10 Total time for retrieving 20000 rows was 40 milliseconds. Total time for retrieving 20000 rows was 30 milliseconds. Total time for retrieving 20000 rows was 40 milliseconds. Number of rows to be prefetched = 100 Total time for retrieving 20000 rows was 20 milliseconds. Total time for retrieving 20000 rows was 20 milliseconds. Total time for retrieving 20000 rows was 10 milliseconds.
Note that the elapsed times shown here are those obtained in my environment. Your elapsed time values may vary, but you should still see an improvement when the prefetch value is increased.