16

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?

Community
  • 1
  • 1
Jeremy
  • 1,015
  • 4
  • 11
  • 20
  • 2
    Please share your measurement results. Note that Oracle has a limitation on batch size. If I'm not mistaken, it's 1000 records at once, so you need to call `executeBatch()` every 1000 records. Related: http://stackoverflow.com/questions/2467125/reusing-a-preparedstatement-multiple-times – BalusC Feb 04 '11 at 19:18
  • I have successuflly used batch sizes of 2500, but I have not seen a real performance improvement beyond a size of 500 –  Feb 04 '11 at 22:20
  • I have used a batch size of 1,000,000 on 12c and saw roughly the same rec/sec ratio as with 1,000 or 10,000 or 100,000 record batches. However, I also force parallelism on the inserts. – access_granted Aug 12 '19 at 16:55

3 Answers3

9

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.

v8-E
  • 1,077
  • 2
  • 14
  • 21
Ronnis
  • 12,593
  • 2
  • 32
  • 52
2

SQL Loader seems to be better way even without direct path loading, but it's hard to maintain. Batch insert 2-4 times faster than single insert statements. Insert all just like batch insert, and both of this would be faster then PL/SQL implementation.

Also you may want to read this AskTom topic.

Lev Khomich
  • 2,247
  • 14
  • 18
1

Using batch can be transparent to a programmer. Here is a cite from here:

Setting the Connection Batch Value

You can specify a default batch value for any Oracle prepared statement in your Oracle connection. > To do this, use the setDefaultExecuteBatch() method of the OracleConnection object. For example, the following code sets the default batch value to 20 for all prepared statement objects associated with the conn connection object:

((OracleConnection)conn).setDefaultExecuteBatch(20);

Even though this sets the default batch value for all the prepared statements of the connection, you can override it by calling setDefaultBatch() on individual Oracle prepared statements.

The connection batch value will apply to statement objects created after this batch value was set.

Sebastian Łaskawiec
  • 2,667
  • 15
  • 33