SQL Server调用executeBatch执行批量更新速度过慢问题求助

1 核心问题定位 #

当前实现存在几个直接导致性能极差的核心问题：

• 关闭自动提交后未显式调用connection.commit()，事务一直悬而未决，数据库侧需等待事务超时或回滚，产生大量额外开销
• 连接串未开启SQL Server JDBC驱动专属的批量操作重写开关，驱动默认会将批量请求拆分为单条SQL逐一执行，完全没有批量效果
• 未做分批提交，单批次数据量过大时会导致数据库事务日志写入压力陡增，执行速度骤降
• 如果切换回Spring JdbcTemplate实现，之前插入生效的-Dspring.jdbc.getParameterType.ignore=true参数同样适用于更新场景，需要保留

2 具体优化步骤 #

2.1 修改JDBC连接串，新增必要参数 #

在原有连接串后追加两个核心参数：

• rewriteBatchedStatements=true：开启驱动批量SQL重写，真正将多条addBatch的操作合并为单次请求发往数据库
• sendStringParametersAsUnicode=false（可选）：如果待更新的name字段是varchar而非nvarchar类型，添加该参数可避免字符串隐式转换导致的索引失效

修改后的连接串示例：
jdbc:sqlserver://mydomain.com:3341;database=my_db;trustServerCertificate=true;encrypt=true;rewriteBatchedStatements=true;sendStringParametersAsUnicode=false;

2.2 补充事务逻辑，增加分批处理 #

补充缺失的事务提交、回滚逻辑，同时添加分批处理规则，每1000-2000条提交一次，避免单批次数据量过大带来的性能损耗。

2.3 验证索引有效性 #

确认更新条件id字段存在主键或唯一索引，避免每条更新都触发全表扫描。

3 优化后代码示例 #

public void batchUpdateSingleTable(List<Map<String, ?>> mapArgTypes) throws SQLException {
    SQLServerDataSource dataSource = new SQLServerDataSource();
    // 追加批量优化相关参数
    dataSource.setURL("jdbc:sqlserver://mydomain.com:3341;database=my_db;trustServerCertificate=true;encrypt=true;rewriteBatchedStatements=true;sendStringParametersAsUnicode=false;");
    dataSource.setUser("user");
    dataSource.setPassword("admin");
    dataSource.setStatementPoolingCacheSize(10000);
    dataSource.setDisableStatementPooling(false);
    dataSource.setServerPreparedStatementDiscardThreshold(10000);
    
    final int BATCH_SIZE = 1000; // 可根据实际业务调整批次大小，建议1000-2000区间
    String updateSql = "update foo..mytable set name=? where id=?";
    Connection connection = null;
    try {
        connection = dataSource.getConnection();
        connection.setAutoCommit(false);
        PreparedStatement prepStmt = connection.prepareStatement(updateSql);
        int count = 0;
        for (Map<String, ?> next : mapArgTypes) {
            prepStmt.setString(1, next.get("name").toString());
            prepStmt.setInt(2, Integer.parseInt(next.get("id").toString()));
            prepStmt.addBatch();
            count++;
            // 达到批次阈值执行并提交
            if (count % BATCH_SIZE == 0) {
                prepStmt.executeBatch();
                connection.commit();
                prepStmt.clearBatch();
            }
        }
        // 执行剩余不足一个批次的记录
        prepStmt.executeBatch();
        connection.commit();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
        // 异常时回滚事务
        if (connection != null) {
            try {
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    } finally {
        // 手动关闭连接避免资源泄漏
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

4 补充说明 #

如果后续切换回Spring JdbcTemplate的batchUpdate方法实现，保留之前的-Dspring.jdbc.getParameterType.ignore=true启动参数即可，该参数可以避免Spring JDBC层的参数类型推导开销，进一步提升性能。

内容的提问来源于stack exchange，提问作者user2342259