Performance Enhancement Samples

This section presents examples that demonstrate Oracle SQLJ row prefetching and update batching performance enhancements.

These samples are located in the following directory:

[Oracle Home]/sqlj/demo

Prefetch Demo--PrefetchDemo.sqlj

This sample has code showing the use of Oracle SQLJ row prefetching, Oracle SQLJ update batching, and Oracle JDBC update batching. (Note that with JDBC 2.0, there is also a standard update-batching paradigm in JDBC.)

The code here does not actually call the Oracle SQLJ update batching method--insertRowsBatchedSQLJ(). That call is commented out. Only the Oracle JDBC update-batching method--insertRowsBatchedJDBC()--is called. But you can compare the code, and you can optionally "comment out" the JDBC update-batching method call and "uncomment" the SQLJ update-batching method call.

For another example of Oracle SQLJ update batching, see "Update Batching--BatchDemo.sqlj".

For information about SQLJ prefetching, see "Row Prefetching". For information about SQLJ update batching, see "Update Batching".

This application uses the following table definition from PrefetchDemo.sql:

DROP TABLE PREFETCH_DEMO;
CREATE TABLE PREFETCH_DEMO (n INTEGER);

Application source code follows:

// Application source code--PrefetchDemo.sqlj
//
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.driver.OraclePreparedStatement;
import sqlj.runtime.ExecutionContext;
import sqlj.runtime.ref.DefaultContext;
import oracle.sqlj.runtime.Oracle;

/**
 Before compiling this demo with online checking, you
 should run the SQL script PrefetchDemo.sql.

 This demo shows how to set different prefetch values for
 SQLJ SELECT statements. It compares SQLJ and JDBC runs.

 Additionally, when creating the data in the PREFETCH_DEMO
 table, we  show how to batch INSERT statements in JDBC.
 SQLJ now also supports batching, and we show the source for
 the equivalent SQLJ batched insert as well.
**/

public class PrefetchDemo
{

  #sql static iterator PrefetchDemoCur (int n);

  public static void main(String[] args) throws SQLException
  {
     System.out.println("*** Start of Prefetch demo ***");

     Oracle.connect(PrefetchDemo.class,"connect.properties");
     OracleConnection conn =
        (OracleConnection) DefaultContext.getDefaultContext().getConnection();
     System.out.println("Connected.");

     try
     {
        try
        {
          #sql { DELETE FROM PREFETCH_DEMO };
        }
        catch (SQLException exn)
        {
          System.out.println("A SQL exception occurred: "+exn);

          System.out.println("Attempting to create the PREFETCH_DEMO table");

          try
          {
             #sql { DROP TABLE PREFETCH_DEMO };
          }
          catch (SQLException ex) { };

          try
          {
             #sql { CREATE TABLE PREFETCH_DEMO (n INTEGER) };
          }
          catch (SQLException ex)
          {
             System.out.println
                   ("Unable to create the PREFETCH_DEMO table: "+exn);
             System.exit(1);
          };
        }
   
        System.out.println
               (">>> Inserting data into the PREFETCH_DEMO table <<<");
   
        // We batch _all_ rows here, so there is only a single roundtrip.
        int numRows = 1000;

        insertRowsBatchedJDBC(numRows, conn);
        // insertRowsBatchedSQLJ(numRows, conn);
   
        System.out.println
               (">>> Selecting data from the PREFETCH_DEMO table <<<");
   
        System.out.println("Default Row Prefetch value is:  " 
                           + conn.getDefaultRowPrefetch());
   
        // We show three row prefetch settings:
        //  1. every row fetched individually
        //  2. prefetching the default number of rows (10)
        //  3. prefetching all of the rows at once
        //
        // each setting is run with JDBC and with SQLJ
   
        int[] prefetch = new int[] { 1, conn.getDefaultRowPrefetch(),
                                     numRows / 10,  numRows };
   
        for (int i=0; i<prefetch.length; i++) 
        {
           selectRowsJDBC(prefetch[i], conn);
           selectRowsSQLJ(prefetch[i], conn, i);
        }
     }
     finally
     { 
       Oracle.close();
     }
  }

  public static void selectRowsSQLJ(int prefetch, OracleConnection conn, int i)
                     throws SQLException
  {
    System.out.print("SQLJ: SELECT using row prefetch "+prefetch+". ");
    System.out.flush();
    conn.setDefaultRowPrefetch(prefetch);

    PrefetchDemoCur c;

    long start = System.currentTimeMillis();

    // Note: In this particular example, statement caching can
    // defeat row prefetch!  Statements are created _with_
    // their prefetch size taken from the connection's prefetch size.
    // The statement will maintain this original prefetch size when
    // it is re-used from the cache.
    //
    // To obtain predictable results, regardless of the cache setting,
    // we must force the use of _different_ select statements for each
    // of the prefetch settings.
    // 
    // To get the seemingly strange behavior above, add the line below
    // and leave statement caching enabled.
    // i=0;

    switch (i % 5) {
      case 0:  #sql c = { SELECT n FROM PREFETCH_DEMO }; break;
      case 1:  #sql c = { SELECT n FROM PREFETCH_DEMO }; break;
      case 2:  #sql c = { SELECT n FROM PREFETCH_DEMO }; break;
      case 3:  #sql c = { SELECT n FROM PREFETCH_DEMO }; break;
      default: #sql c = { SELECT n FROM PREFETCH_DEMO }; 
    }

    while (c.next()) { };
    c.close();
    long delta = System.currentTimeMillis() - start;

    System.out.println("Done in "+(delta / 1000.0)+" seconds.");
  }

  public static void selectRowsJDBC(int prefetch, OracleConnection conn)
                     throws SQLException
  {
    System.out.print("JDBC: SELECT using row prefetch "+prefetch+". ");
    System.out.flush();
    conn.setDefaultRowPrefetch(prefetch);

    long start = System.currentTimeMillis();
    PreparedStatement pstmt =
                      conn.prepareStatement("SELECT n FROM PREFETCH_DEMO");
    ResultSet rs = pstmt.executeQuery();
    while (rs.next()) { };
    rs.close();
    pstmt.close();
    long delta = System.currentTimeMillis() - start;

    System.out.println("Done in "+(delta / 1000.0)+" seconds.");
  }

  public static void insertRowsBatchedSQLJ(int n, OracleConnection conn)
                     throws SQLException
  {
    System.out.print("SQLJ BATCHED: INSERT "+n+" rows. ");
    System.out.flush();

    long start = System.currentTimeMillis();

    ExecutionContext ec = new ExecutionContext();
    ec.setBatching(true);
    ec.setBatchLimit(n);

    for (int i=1; i<=n; i++)
    {
      #sql [ec] { INSERT INTO PREFETCH_DEMO VALUES(:i) };
    }

    ec.executeBatch();

    long delta = System.currentTimeMillis() - start;

    System.out.println("Done in "+(delta / 1000.0)+" seconds.");
  }

  public static void insertRowsBatchedJDBC(int n, OracleConnection conn)
                     throws SQLException
  {
    System.out.print("JDBC BATCHED: INSERT "+n+" rows. ");
    System.out.flush();

    long start = System.currentTimeMillis();
    int curExecuteBatch = conn.getDefaultExecuteBatch();
    conn.setDefaultExecuteBatch(n);

    PreparedStatement pstmt = conn.prepareStatement
                              ("INSERT INTO PREFETCH_DEMO VALUES(?)");
    for (int i=1; i<=n; i++)
    {
      pstmt.setInt(1,i);
      pstmt.execute();
    }
    ((OraclePreparedStatement)pstmt).sendBatch();
    pstmt.close();
    conn.setDefaultExecuteBatch(curExecuteBatch);

    long delta = System.currentTimeMillis() - start;

    System.out.println("Done in "+(delta / 1000.0)+" seconds.");
  }

}

Update Batching--BatchDemo.sqlj

This section shows an example of Oracle SQLJ update batching. For a discussion of how this feature works, see "Update Batching".

This sample uses the following table definition:

DROP TABLE BATCH_DEMO;
CREATE TABLE BATCH_DEMO
            (EMPNO     NUMBER(7), 
             ENAME     VARCHAR2(20),
             HIREDATE  DATE,
             SAL       NUMBER(10, 2)
            );

Application code follows:

// Application source code--BatchDemo.sqlj
//
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import oracle.jdbc.driver.*;
import sqlj.runtime.*;
import oracle.sqlj.runtime.*;

/**
 Before compiling this demo with online checking,
 you must run the SQL script BatchDemo.sql.

 This demo shows the SQLJ batch update feature. 
 **/

public class BatchDemo {
 
  public static void main(String[] args) throws java.sql.SQLException
  {

   System.out.println("*** Batch Demo ***");

   try
   {
     Oracle.connect(BatchDemo.class, "connect.properties");
     System.out.println("Connected.");
    
     try
     {
        #sql { DELETE FROM BATCH_DEMO };
     }
     catch (SQLException e)
     {
        System.out.println("A SQL exception occurred: "+e);

        System.out.println("Attempting to create the BATCH_DEMO table");

        try
        {
           #sql { DROP TABLE BATCH_DEMO };
        }
        catch (SQLException ex) { };

        try
        {
           #sql { CREATE TABLE BATCH_DEMO
                    (EMPNO     NUMBER(7), 
                     ENAME     VARCHAR2(20),
                     HIREDATE  DATE,
                     SAL       NUMBER(10, 2)
                    )
                };
        }
        catch (SQLException ex)
        {
           System.out.println("Unable to create the BATCH_DEMO table: "+ex);
           System.exit(1);
        };
     }

     System.out.println(">>> Inserting 100 records <<<<");
     batchUpdate(1,    100, 201, "test0" );
     batchUpdate(10,   100, 401, "test1" );
     batchUpdate(100,  100, 601, "test2" );
     batchUpdate(1000, 100, 801, "test3" );

     System.out.println(">>> Inserting 1000 records <<<<");
     batchUpdate(1,    1000, 2001, "test0" );
     batchUpdate(10,   1000, 4001, "test1" );
     batchUpdate(100,  1000, 6001, "test2" );
     batchUpdate(1000, 1000, 8001, "test3" );

    }
    finally
    {
      Oracle.close();
    }

    System.out.println("*** End of Demo ***");
  }    
    
  public static void batchUpdate
                (int batchSize, int updateSize, int start, String name)
  throws java.sql.SQLException
  {

     if (batchSize==1)
     {
        System.out.print("Inserting one record at a time: ");
        System.out.flush();
     }
     else
     {
        System.out.print("Inserting in batch of "+batchSize+": ")
        System.out.flush();
     }

     long t = System.currentTimeMillis();

     ExecutionContext ec = new ExecutionContext();

     if (batchSize==1)
     {
       ec.setBatching(false);
     }
     else
     {
       ec.setBatchLimit(batchSize);
       ec.setBatching(true);
     }

     for (int i=start; i<start+updateSize; i++)
     {
        #sql [ec] { insert into  batch_demo(empno, ename,hiredate, sal)
                                 values(:i, :(name+"_"+i), sysdate,  :i )
                  };
     }
     #sql {commit};

     System.out.println("Done in "+((System.currentTimeMillis()-t)/1000.0)
                                            +" seconds.");
 }

}

---