为什么Spring JdbcTemplate要逐行插入进行批量更新？

Question

我有20万行数据需要插入到一个单一的数据库表中。我尝试使用Spring中的jdbcTemplate.batchUpdate每个批次插入10,000行。但是，这个过程耗费太多时间（200K行需要7分钟）。因此，在数据库端，我通过select count(*) from table_X检查插入的行数。我发现插入的行数仅略微增加而不是期望的10K行。是否有人能解释原因或者这是应该在数据库端进行设置的事情？

PS: 我正在使用Sybase....

- Ensom Hodder

---

展示你的代码，你是否在使用BatchPreparedStatementSetter？你是否在你的服务或DAO上使用@Transactional注解？ - Sheetal Mohan Sharma

@SheetalMohanSharma 我放弃了使用Spring，转而使用本地JDBC API并自己处理事务。现在它可以工作了，尽管速度仍然非常慢... - Ensom Hodder

我有效地移除了 @Transactional 注解，这可能是问题的原因......但是我为了加速插入过程而将其删除了。......这并没有帮助解决问题。 - Ensom Hodder

检查JDBC连接设置，我记不清了，但有一些参数可以帮助更快地处理... - Sheetal Mohan Sharma

@SheetalMohanSharma 实际上，我的JDBC连接是通过Spring构建的jdbcTemplate获得的。我尝试在配置文件中设置EnableBulkLoad参数...但它没有起作用或者这个参数没有被正确设置。 :( - Ensom Hodder

这是我在回答中提到的书签..希望能有所帮助 https://dev59.com/3WIj5IYBdhLWcg3wWT2c - Sheetal Mohan Sharma

Answer 1

网络上有很多可用的方法。性能直接取决于：

1. 你编写的代码
2. JDBC驱动程序的使用情况
3. 数据库服务器和连接数量
4. 表索引导致插入速度变慢

没有看过你的代码，任何人都可以猜测，但找不到确切的解决方案。

方法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();
    }
  });
}

参考资料

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

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

方法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);
}

或者，您可以直接执行SQL。

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

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

}

参考

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

方法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
}

方法 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
}

方法三：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();

参考资料

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

/*Happy Coding*/

- Sanka

---

感谢您的回复。我的代码采用了本地JDBC preparedstatement的第三种方法。实际上，插入是通过包含10K行的批处理有效完成的。然而，10K行需要25到30秒才能终止，这是不可接受的 :( - Ensom Hodder

我正在使用Sybase 15.5，猜想可能是Sybase的问题，因为我使用相同的代码测试了PostgreSQL。PostgreSQL在处理1万行时只需不到1秒钟。我已经向DBA团队寻求建议。感谢您上面提供的示例。 - Ensom Hodder

Answer 2

对于我们来说，将代码移动到包装类并使用 @Transactional 注释批量插入方法解决了问题。

- user__42

Answer 3

尝试使用以下连接字符串：useServerPrepStmts=false&rewriteBatchedStatements=true。我还没有尝试过，但这是来自我的书签。您可以按照这些线索进行搜索。

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

- Sheetal Mohan Sharma

---

嗯，但仍然要检查那篇帖子和帖子中的链接 - 这可能会有所帮助。 - Sheetal Mohan Sharma

是的，谢谢您的建议，我会在找到解决方案后更新这篇帖子。 - Ensom Hodder