Oracle and JDBC performance: INSERT ALL vs preparedStatement.addBatch

Question

I have a java app with an Oracle database backend that I need to insert multiple rows into. I've seen the discussion about inserting multiple rows into Oracle, but I'm also interested in how the performance is affected when JDBC in thrown in the mix.

I see a few possibilities:

Option 1: Use a singe-row insert PreparedStatement and execute it multiple times:

String insert = "Insert into foo(bar, baz) values (?, ?)";
PreparedStatement stmt = conn.prepareStatement(insert);
for(MyObject obj : someList) {
    stmt.setString(1, obj.getBar());
    stmt.setString(2, obj.getBaz());
    stmt.execute();
}

Option 2: Build an Oracle INSERT ALL statement:

String insert = "INSERT ALL " +
    "INTO foo(bar, baz), (?, ?) " +
    "INTO foo(bar, baz), (?, ?) " +
    "SELECT * FROM DUAL";
PreparedStatement stmt = conn.prepareStatement(insert);
int i=1;
for(MyObject obj : someList) {
    stmt.setString(i++, obj.getBar());
    stmt.setString(i++, obj.getBaz());
}
stmt.execute();

Option 3: Use the addBatch functionality of PreparedStatement:

String insert = "Insert into foo(bar, baz) values (?, ?)";
PreparedStatement stmt = conn.prepareStatement(insert);
for(MyObject obj : someList) {
    stmt.setString(1, obj.getBar());
    stmt.setString(2, obj.getBaz());
    stmt.addBatch();
}
stmt.execute();

I guess another possibility would be to create a CSV file and use the SQL Loader, but I'm not sure that would really be faster if you add in the overhead of creating the CSV file...

So which option would perform the fastest?

Answer 1

Use the addBatch() functionality of PreparedStatement for anything below 1,000,000 rows.

Each additional component you add to your code increases the dependencies and points of failure.

If you go down that route (external tables, sql loader etc) make sure it is really worth it.

Serializing data to a csv file, moving it into a location readable by database will easily take a second or so.

During that time, I could have inserted 20,000 rows if I just sucked it up and started inserting with JDBC.