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 ....

Answer 1

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

1. Code you have written
2. JDBC driver you are using
3. database server and number of connection you are using
4. 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*/

Answer 2

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