JDBC executeUpdate vs executeBatch Performance

Question

I am curious to know the performance difference between JDBC executeUpdate(multiQuerySqlString) vs JDBC executeBatch() in executing multiple update/insert queries.

For example:

StringBuffer sb = new StringBuffer();
sb.append(updateQuery1+";");
sb.append(updateQuery2+";");
sb.append(insertQuery1+";");
statement.executeUpdate(sb.toString())

vs

preparedStatement = dbConnection.prepareStatement(updateQuery1);
preparedStatement.addBatch();
preparedStatement = dbConnection.prepareStatement(updateQuery2);
preparedStatement.addBatch();
preparedStatement = dbConnection.prepareStatement(insertQuery1);
preparedStatement.addBatch();
preparedStatement.executeBatch();

You use batch incorrectly, it will only execute the last query this way. And `;` separated statements are technically not allowed by JDBC (although some drivers do support it). — Mark Rotteveel, Nov 22 '17 at 15:30

score 3 · Answer 1 · answered Nov 22 '17 at 14:13

It is likely that a batch will be faster than a sequence of executeUpdate (etc) statements because batching reduces the number of Java <-> database round-trips, and may make other optimizations possible.

However, the actual performance difference will depend on the JDBC driver and the back-end database you are using.

The only way to get an answer that is applicable to your (hypothetical) use-case is to benchmark the two alternative ways of implementing the operations with your actual database, driver, SQL and datasets.

Bruno · Answer 2 · 2017-11-22T14:09:33.567

There's two problems in your example:

Multiquery string is vulnerable to SQL Injection, because you will manipulate the query by your self.
Your batch update is wrong. You are reasigning the variable prepared statement. See how to use correctly here: https://www.mkyong.com/jdbc/jdbc-preparedstatement-example-batch-update/

Example of BatchInsert:

dbConnection.setAutoCommit(false);//commit trasaction manually

String insertTableSQL = "INSERT INTO DBUSER"
        + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
        + "(?,?,?,?)";
PreparedStatement = dbConnection.prepareStatement(insertTableSQL);

preparedStatement.setInt(1, 101);
preparedStatement.setString(2, "mkyong101");
preparedStatement.setString(3, "system");
preparedStatement.setTimestamp(4, getCurrentTimeStamp());
preparedStatement.addBatch();

preparedStatement.setInt(1, 102);
preparedStatement.setString(2, "mkyong102");
preparedStatement.setString(3, "system");
preparedStatement.setTimestamp(4, getCurrentTimeStamp());
preparedStatement.addBatch();
preparedStatement.executeBatch();

dbConnection.commit();

JDBC executeUpdate vs executeBatch Performance

2 Answers2