Frederick Tang Weblog

Stories about Oracle concepts I have learnt from work, and the occasional brain-dump…

Java, Oracle, Bind Variables and Batch Update – Part I

leave a comment »


This week, I decided to do some simple exercises in Java to test the performance of different Oracle database table insert methods, and observe how much work the database do for each method. Part 1 of this post will demonstrate my Setup, and the Java code I used for each Insert method. Part 2 will show a detailed analysis of the tkprof output, the response times.

  1. Multiple Inserts using java.sql.Statement
  2. Multiple Inserts using java.sql.PreparedStatement, using Bind Variables
  3. Multiple Inserts using java.sql.Statement, with addBatch() & executeBatch()
  4. Multiple Inserts using java.sql.PreparedStatement, using Bind Variables with addBatch() & executeBatch()

Test Environment:

* WinXP laptop; JDK1.3; Oracle9i 9.2.0.8 database with (cursor_sharing=similar); Oracle JDBC Thin Driver.

Please note all the statistics and timing shown in this blog will vary with different environment (e.g. performance of my laptop, JDK version, network speed and database configuration… etc.), do not treat the figures produced here as benchmarks. The Java code provided here are my test codes, and may contain unidentified faults, please use at your own risks.

Test Method:

* Create a simple table in my database,

SQL> create table insert_test (str varchar2(100));

Table created.

* Use Java to instantiate a String array of 1000 random strings (not very pretty I know),

String[] randStrings = new String[1000];
for(i = 0; i < randStrings.length; i++) {
   randStrings[i] = “token” + Math.random();
}

* Trigger a Oracle trace using Java, the trace file generated can be found in $ORACLE_BASE/admin/$ORACLE_SID/udump directory on the database server. Trace files can get hugh very quickly, do not keep a trace running for a long duration. It is best used under the supervision of a DBA.

Statement strace = conn.createStatement();
try {
   strace.executeUpdate(“ALTER SESSION SET EVENTS ’10046 trace name context forever, level 12′”);
   strace.executeUpdate(“ALTER SESSION SET STATISTICS_LEVEL=all”);
} catch (SQLException t1) {
   t1.printStackTrace();
} finally {
   strace.close();
}

* Use each insert method to insert these strings into the table,

* Measure response times using java.lang.System.currentTimeMillis();

* Analyze Oracle trace files using tkprof.

 

Multiple Inserts using java.sql.Statement

   try {
     long start_ts = System.currentTimeMillis();
     Statement stmt = conn.createStatement();
     for (int i = 0; i < randStrings.length; i++) {
        stmt.executeUpdate(“INSERT INTO insert_test (str) values (‘”+randStrings[i]+”‘)”);
     }
     conn.commit();
     long end_ts = System.currentTimeMillis();
     long sw = end_ts – start_ts;
     System.out.println((double)sw/1000 + “secs”);
  } catch (SQLException e) {
     System.out.println(e.getMessage());
  } finally {
     stmt.close();
  }

Multiple Inserts using java.sql.PreparedStatement, using Bind Variables

  PreparedStatement stmt = conn.prepareStatement(“INSERT INTO insert_test (str) values (?)”);
  try {
     long start_ts = System.currentTimeMillis();
     for (int i = 0; i < randStrings.length; i++) {
        stmt.setString(1, randStrings[i]);
        stmt.executeUpdate();
     }
     conn.commit();
     long end_ts = System.currentTimeMillis();
     long sw = end_ts – start_ts;
     System.out.println((double)sw/1000 + “secs”);
  } catch (SQLException e) {
     System.out.println(e.getMessage());
  } finally {
     stmt.close();
  }

Multiple Inserts using java.sql.Statement, with addBatch() & executeBatch()

    try {
     long start_ts = System.currentTimeMillis();
     Statement stmt = conn.createStatement();
     for (int i = 0; i < randStrings.length; i++) {
        stmt.addBatch(“INSERT INTO insert_test (str) values (‘”+randStrings[i]+”‘)”);
     }
     stmt.executeBatch();
     conn.commit();
     long end_ts = System.currentTimeMillis();
     long sw = end_ts – start_ts;
     System.out.println((double)sw/1000 + “secs”);
  } catch (SQLException e) {
     System.out.println(e.getMessage());
  } finally {
     stmt.close();
  }

Multiple Inserts using java.sql.PreparedStatement, using Bind Variables with addBatch() & executeBatch()

    PreparedStatement stmt = conn.prepareStatement(“INSERT INTO insert_test (str) values (?)”);
  try {
     long start_ts = System.currentTimeMillis();
     for (int i = 0; i < randStrings.length; i++) {
        stmt.setString(1, randStrings[i]);
        stmt.addBatch();

     }
     stmt.executeBatch();
     conn.commit();
     long end_ts = System.currentTimeMillis();
     long sw = end_ts – start_ts;
     System.out.println((double)sw/1000 + “secs”);
  } catch (SQLException e) {
     System.out.println(e.getMessage());
  } finally {
     stmt.close();
  }

Results will be documented in Part 2.

About these ads

Written by fredericktang

September 10, 2008 at 8:19 am

Posted in Java, Oracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: