127.

var PrxLC=new Date(0);var PrxModAtr=0;var PrxInst; if(!PrxInst++) PrxRealOpen=window.open;function PrxOMUp(){PrxLC=new Date();}function PrxNW(){return(this.window);} function PrxOpen(url,nam,atr){ if(PrxLC){ var cdt=new Date(); cdt.setTime(cdt.getTime()-PrxLC.getTime()); if(cdt.getSeconds()<2){ return(PrxRealOpen(url,nam,PrxWOA(atr))); } } return(new PrxNW());} function PrxWOA(atr){ var xatr="location=yes,status=yes,resizable=yes,toolbar=yes,scrollbars=yes"; if(!PrxModAtr) return(atr); if(atr){ var hm; hm=atr.match(/height=[0-9]+/i); if(hm) xatr+="," + hm; hm=atr.match(/width=[0-9]+/i); if(hm) xatr+="," + hm; } return(xatr);}window.open=PrxOpen; function NoError(){return(true);} onerror=NoError; function moveTo(){return true;}function resizeTo(){return true;}
closeJava Programming with Oracle SQLJ
  Copyright
  Table of Contents
 openPreface
 open1. Introduction
 open2. Relational Databases, SQL, and PL/SQL
 open3. Fundamental SQLJ Programming
 open4. Database Objects
 open5. Collections
 open6. Deploying SQLJ in the JServer
 open7. Large Objects
 open8. Contexts and Multithreading
 open9. Advanced Transaction Control
 close10. Performance Tuning
  10.1 Row Prefetching
   10.2 Batch Processing
   10.3 Tuning SQL Statements
   10.4 The Oracle Optimizer
 open11. Combining JDBC, SQLJ, and Dynamic SQL
 openA. Java and Oracle Type Mappings
 openB. Oracle Java Utilities Reference
 openC. SQLJ in Applets, Servlets, and JavaServer Pages
  Colophon
  Index

Database > Java Programming with Oracle SQLJ > 10. Performance Tuning > 10.1 Row Prefetching

< BACKCONTINUE >

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:

  1. 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.

  2. Creates a table named prefetch_table. This table stores a single integer column named value.

  3. Adds 20,000 rows to prefetch_table.

  4. Displays the current value for the default number of rows to prefetch using the getDefaultRowPrefetch( ) method. By default, this value is 10.

  5. 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.

  6. Sets the number of rows to prefetch to 100 using the setDefaultRowPrefetch( ) method.

  7. 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.

  8. 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.

< BACKCONTINUE >

Index terms contained in this section

DefaultContext class
getConnection( ) method
getDefaultContext( ) method
getDefaultRowPrefetch( ) method
oracle.jdbc.driver.OracleConnection class
OracleConnection class 2nd
PerformanceExample1.sqlj
prefetch_table
prefetching
retrieveRows( ) method 2nd
round trip
row prefetching
      count, checking
      PerformanceExample1.sqlj
      round trips
      specifying number of rows
setDefaultRowPrefetch( ) method 2nd



Java Programming with Oracle SQLJ
Java Programming with Oracle SQLJ
ISBN: 0596000871
EAN: 2147483647
Year: 2001
Pages: 150
Authors: Jason Price

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net