why spring jdbcTemplate batchUpdate insert row by row

Question

I have 200K rows to be inserted in one single database table. I tried to use jdbcTemplate.batchUpdate in spring in order to do insertion 10,000 per batch. However, this process consumes too much time (7 mins for 200K rows). So on database side, I check the number of rows inserted by select count(*) from table_X. I found the number of rows increased slightly instead of 10K expected. Can anyone explain what's reason or is it something which should be configurated on Database side ?

PS: I am using sybase ....

Show your code, are you using BatchPreparedStatementSetter ? Are you using @Transactional annotation on your service or DAO? — Sheetal Mohan Sharma, Sep 19 '16 at 15:04
@SheetalMohanSharma Instead of using spring, I switched to native jdbc api and handles the transactioin myself. It works now. although it's still very slow.... — Ensom Hodder, Sep 19 '16 at 15:23
effectively i removed @Transactional annotation which could cause the problem....But I removed it in order to accelerate the inseration. ...It didn't help — Ensom Hodder, Sep 19 '16 at 15:25
Check the jdbc connection setting, cant recall but there were some params that helped faster processing... — Sheetal Mohan Sharma, Sep 19 '16 at 15:26
@SheetalMohanSharma in fact, my jdbc connection is obtained via jdbcTemplate which was constructed by Spring. I tried to set EnableBulkLoad parameter in the configuration file...but it didn't work or this parameter was not correctly setted. :( — Ensom Hodder, Sep 19 '16 at 15:31
Here is the bookmark that I refereed to in my answer..hope that helps http://stackoverflow.com/questions/20360574/why-springs-jdbctemplate-batchupdate-so-slow — Sheetal Mohan Sharma, Sep 19 '16 at 15:36

score 35 · Answer 1 · edited Sep 09 '19 at 19:54

There are lot of approaches available on the web. Performance directly depends on the

Code you have written
JDBC driver you are using
database server and number of connection you are using
table indexes leads to slowness for insertion

Without looking at your code anyone can guess, but no one can find the exact solution.

Approach 1

//insert batch example
public void insertBatch(final List<Customer> customers){

  String sql = "INSERT INTO CUSTOMER " +
    "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";

  getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {

    @Override
    public void setValues(PreparedStatement ps, int i) throws SQLException {
        Customer customer = customers.get(i);
        ps.setLong(1, customer.getCustId());
        ps.setString(2, customer.getName());
        ps.setInt(3, customer.getAge() );
    }

    @Override
    public int getBatchSize() {
        return customers.size();
    }
  });
}

reference

https://www.mkyong.com/spring/spring-jdbctemplate-batchupdate-example/

http://docs.spring.io/spring-framework/docs/3.0.0.M4/reference/html/ch12s04.html

Approach 2.1

//insert batch example
public void insertBatch(final List<Customer> customers){
    String sql = "INSERT INTO CUSTOMER " +
        "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";

    List<Object[]> parameters = new ArrayList<Object[]>();

    for (Customer cust : customers) {
        parameters.add(new Object[] {cust.getCustId(),
            cust.getName(), cust.getAge()}
        );
    }
    getSimpleJdbcTemplate().batchUpdate(sql, parameters);
}

Alternatively, you can execute the SQL directly.

//insert batch example with SQL
public void insertBatchSQL(final String sql){

    getJdbcTemplate().batchUpdate(new String[]{sql});

}

reference

https://www.mkyong.com/spring/spring-simplejdbctemplate-batchupdate-example/

Approach 2.2

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcTemplate simpleJdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
    }

    public int[] batchUpdate(final List<Actor> actors) {
        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(actors.toArray());
        int[] updateCounts = simpleJdbcTemplate.batchUpdate(
            "update t_actor set first_name = :firstName, last_name = :lastName where id = :id",
            batch);
        return updateCounts;
    }

    //  ... additional methods
}

Approach 2.3

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcTemplate simpleJdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
    }

    public int[] batchUpdate(final List<Actor> actors) {
        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 = simpleJdbcTemplate.batchUpdate(
                "update t_actor set first_name = ?, last_name = ? where id = ?",
                batch);
        return updateCounts;
    }

    //  ... additional methods
}

Approach 3 :JDBC

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();

reference

https://www.mkyong.com/jdbc/jdbc-preparedstatement-example-batch-update/

/*Happy Coding*/

Thanks for your reply. My code was approach 3 with native JDBC preparedstatement. In fact. the insertion is effectively done by batch which includes 10K rows. However 10K rows tooks 25 to 30 seconds to terminate which is unacceptable :( — Ensom Hodder, Sep 20 '16 at 12:48
I am using Sybase 15.5, and guess it could be problem with Sybase, because I tested with PostgreSQL with the same code. PostgreSQL takes less than 1 sec for 10K rows. I had asked DBA team for advices. Thanks for your examples above. — Ensom Hodder, Sep 20 '16 at 12:50

score 1 · Answer 2 · answered Sep 19 '16 at 15:35

1

Try setting below for connection string - useServerPrepStmts=false&rewriteBatchedStatements=true. Have not tried but its from my bookmarks. You can search on these lines..

Connection c = DriverManager.getConnection("jdbc:<db>://host:<port>/db?useServerPrepStmts=false&rewriteBatchedStatements=true", "username", "password");

answered Sep 19 '16 at 15:35

Sheetal Mohan Sharma

2,908
1
23
24

Hmm but still check that post and links in that post - it may help – Sheetal Mohan Sharma Sep 19 '16 at 15:47
yes thanks for your advice, i will update this post when I find a solution – Ensom Hodder Sep 19 '16 at 15:48

score 1 · Answer 3 · answered Jan 18 '21 at 13:28

1

For us moving the code to a wrapper class and annotating the batch insert method with @Transactional did solve the problem.

answered Jan 18 '21 at 13:28

user__42

543
4
13

why spring jdbcTemplate batchUpdate insert row by row

3 Answers3

Linked