我正在尝试找到更快的批量插入方法.
我试图用jdbcTemplate.update(String sql)插入几个批处理,其中sql是由StringBuilder 构建的,如下所示:
INSERT INTO TABLE(x, y, i) VALUES(1,2,3), (1,2,3), ... , (1,2,3)
批量大小正好是1000.我插入了近100批.我使用StopWatch检查了时间并找出了插入时间:
min[38ms], avg[50ms], max[190ms] per batch
我很高兴,但我想让我的代码变得更好.
之后,我尝试使用jdbcTemplate.batchUpdate,如:
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { // ... } @Override public int getBatchSize() { return 1000; } });
sql的样子
INSERT INTO TABLE(x, y, i) VALUES(1,2,3);
我很失望!jdbcTemplate以分开的方式执行1000行批处理的每个插入.我在mysql_log上找到了,发现有一千个插入.我使用StopWatch检查了时间并找出了插入时间:
min [900ms],avg [1100ms],每批最大[2000ms]
那么,任何人都可以向我解释一下,为什么jdbcTemplate在这个方法中做了单独的插入?为什么方法的名称是batchUpdate?或者可能是我以错误的方式使用这种方法?
JDBC连接URL中的这些参数可以对批处理语句的速度产生很大影响 - 根据我的经验,它们可以加快速度:
?useServerPrepStmts =假rewriteBatchedStatements =真
请参阅:JDBC批处理插入性能
我也遇到过与Spring JDBC模板相同的问题.可能在Spring Batch中,语句在每个插件或块上执行并提交,这会减慢速度.
我用原始JDBC批处理插入代码替换了jdbcTemplate.batchUpdate()代码,并找到了主要的性能改进.
DataSource ds = jdbcTemplate.getDataSource(); Connection connection = ds.getConnection(); connection.setAutoCommit(false); String sql = "insert into employee (name, city, phone) values (?, ?, ?)"; PreparedStatement ps = connection.prepareStatement(sql); final int batchSize = 1000; int count = 0; for (Employee employee: employees) { ps.setString(1, employee.getName()); ps.setString(2, employee.getCity()); ps.setString(3, employee.getPhone()); ps.addBatch(); ++count; if(count % batchSize == 0 || count == employees.size()) { ps.executeBatch(); ps.clearBatch(); } } connection.commit(); ps.close();
检查此链接以及 JDBC批量插入性能
我发现在调用中设置argTypes数组有一个重大改进。
就我而言,对于Spring 4.1.4和Oracle 12c,要插入具有35个字段的5000行:
jdbcTemplate.batchUpdate(insert, parameters); // Take 7 seconds jdbcTemplate.batchUpdate(insert, parameters, argTypes); // Take 0.08 seconds!!!
argTypes参数是一个int数组,您可以通过这种方式设置每个字段:
int[] argTypes = new int[35]; argTypes[0] = Types.VARCHAR; argTypes[1] = Types.VARCHAR; argTypes[2] = Types.VARCHAR; argTypes[3] = Types.DECIMAL; argTypes[4] = Types.TIMESTAMP; .....
我调试了org \ springframework \ jdbc \ core \ JdbcTemplate.java,发现大部分时间都花在试图了解每个字段的性质上,而这是针对每条记录进行的。
希望这可以帮助 !
只需使用交易.在方法上添加@Transactional.
如果使用多个数据源@Transactional("dsTxManager"),请务必声明正确的TX管理器.我有一个插入60000记录的情况.大约需要15秒.没有其他调整:
@Transactional("myDataSourceTxManager") public void save(...) { ... jdbcTemplate.batchUpdate(query, new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { ... } @Override public int getBatchSize() { if(data == null){ return 0; } return data.size(); } }); }
将您的sql插入更改为INSERT INTO TABLE(x, y, i) VALUES(1,2,3)
.框架为您创建一个循环.例如:
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(); } }); }
如果你有这样的东西.Spring会做类似的事情:
for(int i = 0; i < getBatchSize(); i++){ execute the prepared statement with the parameters for the current iteration }
框架首先从查询(sql
变量)创建PreparedStatement,然后调用setValues方法并执行语句.重复的次数与getBatchSize()
方法中指定的次数相同.因此,编写insert语句的正确方法是只有一个values子句.你可以看看http://docs.spring.io/spring/docs/3.0.x/reference/jdbc.html