Fastest 'update' on jdbc with PreparedStatement and executeBatch

Question

I have a java program that in some circumstances must update a large amount of records in a database (e.g 100,000).

The way it does it is by creating a PreparedStatement and by using the addBatch technique. Here is the snippet:

connection.setAutoCommit(false);
PreparedStatement ps = connection.prepareStatement(
        "UPDATE myTable SET colName=? where id=?");

for (...) { // this loop can be 100000 long 
     colValue = ...
     id = ...
     ps.setString(1,colValue);
     ps.setString(2,id);
     ps.addBatch();
  }

ps.executeBatch();
connection.commit();

is this the best (fastest) way to update 100000 of records in JDBC ?

Could anybody suggest a better way ?

Answer 1

You should use Spring Batch operations with the JdbcTemplate

Answer 2

Try this as a benchmark:

1. Use the built-in SQL tools to do a bulk extract of the entire table. All rows. All columns.

2. Drop (or rename) the table.

3. Use a simple flat-file read/write to create a new file with the updates applied.

4. Use the bulk-load utility that comes with your database to rebuild the entire table from the extracted file.

5. Add indexes after the reload.

You may find that this is faster than any SQL solution. We stopped using UPDATES for a data warehouse because extract -> flat file process -> load was much faster than SQL.

Answer 3

Since batching uses buffering on client side and then sends everything as a single request, it might be wise to execute batches with 5000 rows. You should watch you memory consumption when adding 100.000 rows.

Sometime it works faster to push data in several loads instead of 1 single load(using JDBC, at least based on my previous experience).