With Oracle, there are two batch update techniques; standard JDBC batch updates and Oracle batch updates. We will look at both of these and demonstrate the performance benefits of each.
This simple table is used for our tests.
Table created.CREATE TABLE batch_test
( id NUMBER,
name VARCHAR2(50)
);
Without Batching
First we will execute an INSERT statement ten thousand times without batching. This will give us a baseline to compare with the batched examples later on.Autocommit has been disabled (as it always should be) and the updates are committed explicitly after one hundred rows.
public static void testNoBatching() throws SQLException {Connection con = ConnectionFactory.getNewConnection();
PreparedStatement pstmt =
con.prepareStatement("INSERT INTO batch_test (id, name) " +
"VALUES (?, rpad('x', 50, 'x'))");long start = System.currentTimeMillis();
for (int i = 1; i <= 10000; i++) {
pstmt.setInt(1, i);
pstmt.executeUpdate();
if (i % 100 == 0) {
con.commit();
}
}System.out.printf("Inserting 10,000 rows took %4.2f seconds\n",
(System.currentTimeMillis() - start) / 1000f);
pstmt.close();
con.close();
}------------------------------------------------------------------------------Inserting 10,000 rows took 12.83 seconds
This test uses a PreparedStatement and bind variables to ensure that the SQL is executed as efficiently as possible. Even so, without batching, the inserts took nearly 13 seconds.
Standard JDBC Batch Updates
With standard JDBC batch updates, the SQL statements are batched and submitted explicitly.The addBatch method adds an SQL statement to the current batch. At an appropriate point the batch is submitted to Oracle by calling the executeBatch method. Here we submit the batch after one hundred inserts, just before we commit. executeBatch returns an array of integers which contains the number of rows affected by each statement in the batch.public static void testStandardBatching() throws SQLException {Connection con = ConnectionFactory.getNewConnection();
PreparedStatement pstmt =
con.prepareStatement("INSERT INTO batch_test (id, name) " +
"VALUES (?, rpad('x', 50, 'x'))");if (!con.getMetaData().supportsBatchUpdates()) {
throw new SQLException("Batch updates not supported");
}long start = System.currentTimeMillis();
for (int i = 1; i <= 10000; i++) {
pstmt.setInt(1, i);
pstmt.addBatch();
if (i % 100 == 0) {
int[] updateCounts = pstmt.executeBatch();
con.commit();
}
}System.out.printf("Inserting 10,000 rows took %4.2f seconds\n",
(System.currentTimeMillis() - start) / 1000f);pstmt.close();
con.close();
}------------------------------------------------------------------------------Inserting 10,000 rows took 0.40 seconds
This time, the entire test took less than half a second, a fraction of the time taken to perform the original test.
Oracle Batch Updates
Oracle update batching takes a different approach. The batch size is set on the OraclePreparedStatement object and SQL statements are implicitly batched by the JDBC driver until the batch size is reached. At this point the SQL is automatically submitted to Oracle.This time we implement batching with a single call to the setExecuteBatch method on OraclePreparedStatement. The update count returned from executeUpdate will return zero if the call is adding the statement to the batch, or the total number of rows affected by all the update statements if the batch is being submitted to Oracle. Once again, with the batch size set to one hundred, the ten thousand rows are inserted in less than half a second.public static void testOracleBatching() throws SQLException {Connection con = ConnectionFactory.getNewConnection();
PreparedStatement pstmt =
con.prepareStatement("INSERT INTO batch_test (id, name) " +
"VALUES (?, rpad('x', 50, 'x'))");
((OraclePreparedStatement)pstmt).setExecuteBatch (100);
long start = System.currentTimeMillis();
for (int i = 1; i <= 10000; i++) {
pstmt.setInt(1, i);
int updateCount = pstmt.executeUpdate();
if (i % 100 == 0) {
con.commit();
}
}System.out.printf("Inserting 10,000 rows took %4.2f seconds\n",
(System.currentTimeMillis() - start) / 1000f);pstmt.close();
con.close();
}------------------------------------------------------------------------------Inserting 10,000 rows took 0.39 seconds
Summary
These examples show that there can be a significant improvement in performance when batch updates are used rather than single statement updates. In our tests, execution times were reduced from over twelve seconds to less than half a second when batching was introduced.Of course, not every application is suited to batch updates but, when used appropriately, the performance benefits can be considerable.
