Performance and limitation issues between update() and batchUpdate() methods of NamedParameterJdbcTemplate

Question

I would like to know when to use update() or bacthUpdate() method from NamedParameterJdbcTemplate class of Spring framework.

Is there any row limitation for update()? How many rows can handle update() without having performance issues or hanging my db? Starting from how many rows batchUpdate() is getting good performance?

Thanks.

Answer 1

Bellow is my viewpoint:

when to use update() or bacthUpdate() method from NamedParameterJdbcTemplate class of Spring framework

You should use bacthUpdate() so long as when you need to execute multiple sql together.

Is there any row limitation for update()?

This should depends on the DB you use. But I haven't met row limitation for updating. Of course，updating few rows are faster than updating many rows.(such as, UPDATE ... WHERE id=1 vs UPDATE ... WHERE id > 1).

How many rows can handle update() without having performance issues or hanging my db?

This isn't sure. This depends on the DB you using, Machine Performance, etc. If you want to know the exact result, you can view the DB Vendor's Benchmark, or you can measure it by some tests.

Starting from how many rows batchUpdate() is getting good performance?

In fact, batchUpdate() is commonly used when you do batch INSERT, UPDATE or DELETE, this will improve much performance. such as:

BATCH INSERT:

SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(employees.toArray());
int[] updateCounts = namedParameterJdbcTemplate.batchUpdate("INSERT INTO EMPLOYEE VALUES (:id, :firstName, :lastName, :address)", batch);
return updateCounts;

BATCH UPDATE:

List<Object[]> batch = new ArrayList<Object[]>();
    for (Actor actor : actors) {
        Object[] values = new Object[] {
                actor.getFirstName(),
                actor.getLastName(),
                actor.getId()};
        batch.add(values);
    }
    int[] updateCounts = jdbcTemplate.batchUpdate(
            "update t_actor set first_name = ?, last_name = ? where id = ?",
            batch);
    return updateCounts;

Internally, batchUpdate() will use PreparedStatement.addBatch(), you can view some spring jdbc tutorial.. Batch operations sent to the database in one "batch", rather than sending the updates one by one. Sending a batch of updates to the database in one go, is faster than sending them one by one, waiting for each one to finish. There is less network traffic involved in sending one batch of updates (only 1 round trip), and the database might be able to execute some of the updates in parallel. In addition, the DB Driver must support batch operation when you use batchUpdate() and batchUpdate() isn't in one transaction in default.

More details you can view:

https://docs.spring.io/spring/docs/current/spring-framework-reference/html/jdbc.html#jdbc-advanced-jdbc http://tutorials.jenkov.com/jdbc/batchupdate.html#batch-updates-and-transactions

Hope you have to help.