2009-08-31

Load using Java

Cary Millsap is writing good things about measuring when tuning performance. His paper Making friends "Optimizing the insert program" is talking how to insert 10000 rows using Java. There are presented only possibilities to insert using Statement in a loop or using PreparedStatement in a loop. Should there be a alternative way also presented? Avoid looping statements in Java and populate all rows just in single call to the database.

Timing results inserting 10000 rows:

Array
Executed in 0 min 0 s 313 ms.
Prepared
Executed in 0 min 2 s 985 ms.


ARRAY:

create table si (s number(19),s2 number(19));
create or replace type nums is object ( n1 number(19), n2 number(19));
create type sit is table of nums;

private static void insertArray(Connection c, List elems)
throws SQLException {
ArrayDescriptor ad = ArrayDescriptor.createDescriptor("SIT", c);
ARRAY a = new ARRAY(ad, c, elems.toArray());
OraclePreparedStatement ops
= (OraclePreparedStatement)c.prepareStatement(
"insert into si " +
" select *" +
" from table(?)");
ops.setARRAY(1, a);

System.out.println(ops.executeUpdate());
ops.close();
c.commit();
}


Prepared:

private static void insertPrepared(Connection c, List elems)
throws SQLException {
PreparedStatement ps = c.prepareStatement("insert into si values(?,?)");
Iterator i = elems.iterator();
while ( i.hasNext())
{
Object[] o = i.next();
ps.setInt(1, (Integer)o[0]);
ps.setInt(2, (Integer)o[1]);
ps.executeUpdate();
}
ps.close();

c.commit();
}


Here are some timings for other number of rows. It shows that if you are inserting 100-1000 rows this approach might be worth considering. Measure yourself. Be sure to have enough memory available for your Java.


ms localhost remote db
rows prepared array prepared array
10 94 140 93 141
100 125 141 125 156
1000 313 203 844 265
10000 1500 344 16297 453
100000 12063 1422 206210 2078
1000000 182063 java.lang.OutOfMemoryError: Java heap space


insertintoselect.java

1 comment:

  1. A batch update might also have something similar boost to your performance and even without additional need for pl/sql types.

    ReplyDelete

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.