Why spring jdbcTemplate batchUpdate insert row by row

Question

I have 200K rows that need to be inserted into one database table. I tried using jdbcTemplate.batchUpdatespring to insert 10,000 per packet. However, this process takes too much time (7 minutes for 200K lines). Therefore, on the database side, I check the number of rows inserted in select count(*) from table_X. I found that the number of rows increased slightly, expecting 10K. Can someone explain the reason or is it something that needs to be configured on the database side?

PS: I use sybase ....

Answer 1

There are many approaches available online. Performance directly depends on

1. The code you wrote
2. The JDBC driver you are using
3. database server and the number of the connection you are using
4. Tabular indices slow down 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();
    }
  });
}

link

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

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

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

}

link

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

link

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

/*Happy Coding*/

Answer 2

- useServerPrepStmts=false&rewriteBatchedStatements=true. , . .

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

Answer 3

in these approaches, which approach will give the best performance, and which was fast.