用 JDBC 实现批量 INSERTS 的有效方法

在我的应用程序，我需要做很多插入。它是一个 Java 应用程序，我使用纯 JDBC 来执行查询。数据库就是甲骨文。但是我已经启用了批处理，因此它节省了执行查询的网络延迟。但是查询以单独的 INSERT 顺序执行:

insert into some_table (col1, col2) values (val1, val2)
insert into some_table (col1, col2) values (val3, val4)
insert into some_table (col1, col2) values (val5, val6)

我想知道以下形式的 INSERT 是否更有效:

insert into some_table (col1, col2) values (val1, val2), (val3, val4), (val5, val6)

即将多个 INSERT 折叠成一个。

还有其他提高批处理 INSERT 速度的技巧吗？

187058 次浏览
小开

显然，您必须进行基准测试，但是如果使用 PreparedStatement 而不是 Statement，那么通过 JDBC 发出多个插入会快得多。

小开

Statement为您提供以下选项:

Statement stmt = con.createStatement();


stmt.addBatch("INSERT INTO employees VALUES (1000, 'Joe Jones')");
stmt.addBatch("INSERT INTO departments VALUES (260, 'Shoe')");
stmt.addBatch("INSERT INTO emp_dept VALUES (1000, 260)");


// submit a batch of update commands for execution
int[] updateCounts = stmt.executeBatch();
小开

这是前面两个答案的混合体:

  PreparedStatement ps = c.prepareStatement("INSERT INTO employees VALUES (?, ?)");


ps.setString(1, "John");
ps.setString(2,"Doe");
ps.addBatch();


ps.clearParameters();
ps.setString(1, "Dave");
ps.setString(2,"Smith");
ps.addBatch();


ps.clearParameters();
int[] results = ps.executeBatch();
小开

使用 INSERTALL 语句如何？

INSERT ALL


INTO table_name VALUES ()


INTO table_name VALUES ()


...


SELECT Statement;

我记得为了让这个请求成功，最后一个 select 语句是必需的，但是不记得为什么了。 您也可以考虑使用 准备声明代替。有很多优点！

法里德

小开

如果迭代次数较少，那么使用 PreparedStatement 将比使用语句慢得多。为了从对语句使用 PrepareStatement 中获得性能优势，需要在迭代次数至少为50或更多的循环中使用该语句。

小开

可以在 java 中使用 addBatch 和 ExecuteBatch 进行批量插入

小开

尽管这个问题问的是 使用 JDBC 有效地插入 Oracle，但我目前正在使用 DB2(在 IBM 大型机上) ，从概念上来说插入是相似的，所以我认为在两者之间查看我的度量标准可能会有所帮助

  • 一次插入一个记录

  • 插入一批记录(非常有效)

数据出来了

1)一次插入一条记录

public void writeWithCompileQuery(int records) {
PreparedStatement statement;


try {
Connection connection = getDatabaseConnection();
connection.setAutoCommit(true);


String compiledQuery = "INSERT INTO TESTDB.EMPLOYEE(EMPNO, EMPNM, DEPT, RANK, USERNAME)" +
" VALUES" + "(?, ?, ?, ?, ?)";
statement = connection.prepareStatement(compiledQuery);


long start = System.currentTimeMillis();


for(int index = 1; index < records; index++) {
statement.setInt(1, index);
statement.setString(2, "emp number-"+index);
statement.setInt(3, index);
statement.setInt(4, index);
statement.setString(5, "username");


long startInternal = System.currentTimeMillis();
statement.executeUpdate();
System.out.println("each transaction time taken = " + (System.currentTimeMillis() - startInternal) + " ms");
}


long end = System.currentTimeMillis();
System.out.println("total time taken = " + (end - start) + " ms");
System.out.println("avg total time taken = " + (end - start)/ records + " ms");


statement.close();
connection.close();


} catch (SQLException ex) {
System.err.println("SQLException information");
while (ex != null) {
System.err.println("Error msg: " + ex.getMessage());
ex = ex.getNextException();
}
}
}

100个交易的指标:

each transaction time taken = 123 ms
each transaction time taken = 53 ms
each transaction time taken = 48 ms
each transaction time taken = 48 ms
each transaction time taken = 49 ms
each transaction time taken = 49 ms
...
..
.
each transaction time taken = 49 ms
each transaction time taken = 49 ms
total time taken = 4935 ms
avg total time taken = 49 ms

第一个事务在 120-150ms附近执行，然后执行，后续的事务只在 50ms附近执行。(这个值仍然很高，但是我的数据库位于不同的服务器上(我需要对网络进行故障排除)

2)通过 preparedStatement.executeBatch()实现批量插入(有效) 

public int[] writeInABatchWithCompiledQuery(int records) {
PreparedStatement preparedStatement;


try {
Connection connection = getDatabaseConnection();
connection.setAutoCommit(true);


String compiledQuery = "INSERT INTO TESTDB.EMPLOYEE(EMPNO, EMPNM, DEPT, RANK, USERNAME)" +
" VALUES" + "(?, ?, ?, ?, ?)";
preparedStatement = connection.prepareStatement(compiledQuery);


for(int index = 1; index <= records; index++) {
preparedStatement.setInt(1, index);
preparedStatement.setString(2, "empo number-"+index);
preparedStatement.setInt(3, index+100);
preparedStatement.setInt(4, index+200);
preparedStatement.setString(5, "usernames");
preparedStatement.addBatch();
}


long start = System.currentTimeMillis();
int[] inserted = preparedStatement.executeBatch();
long end = System.currentTimeMillis();


System.out.println("total time taken to insert the batch = " + (end - start) + " ms");
System.out.println("total time taken = " + (end - start)/records + " s");


preparedStatement.close();
connection.close();


return inserted;


} catch (SQLException ex) {
System.err.println("SQLException information");
while (ex != null) {
System.err.println("Error msg: " + ex.getMessage());
ex = ex.getNextException();
}
throw new RuntimeException("Error");
}
}

一批100个事务的指标是

total time taken to insert the batch = 127 ms

以及1000笔交易

total time taken to insert the batch = 341 ms

因此，在 ~5000ms中进行100个事务(每次使用一个 trxn)将减少到 ~150ms(使用一批100条记录)。

注意-忽略我的网络是超级慢，但指标值将是相对的。

小开

在我的代码中，我没有直接访问“ preredStatement”，所以我不能使用批处理，我只是将查询和参数列表传递给它。然而，诀窍是创建一个可变长度的 insert 语句和一个参数的 LinkedList。其效果与上面的示例相同，只是输入长度可变。见下文(省略错误检查)。 假设“ myTable”有3个可更新字段: f1、 f2和 f3

String []args={"A","B","C", "X","Y","Z" }; // etc, input list of triplets
final String QUERY="INSERT INTO [myTable] (f1,f2,f3) values ";
LinkedList params=new LinkedList();
String comma="";
StringBuilder q=QUERY;
for(int nl=0; nl< args.length; nl+=3 ) { // args is a list of triplets values
params.add(args[nl]);
params.add(args[nl+1]);
params.add(args[nl+2]);
q.append(comma+"(?,?,?)");
comma=",";
}
int nr=insertIntoDB(q, params);

在我的 DBInterface 类中，我有:

int insertIntoDB(String query, LinkedList <String>params) {
preparedUPDStmt = connectionSQL.prepareStatement(query);
int n=1;
for(String x:params) {
preparedUPDStmt.setString(n++, x);
}
int updates=preparedUPDStmt.executeUpdate();
return updates;
}
小开

可以使用这个 rewriteBatchedStatements参数使批量插入更快。

你可以在这里读到 param: MySQL 和 JDBC 使用 rewriteBatechStatments = true

小开

SQLite: 以上答案都是正确的。对于 SQLite，它有一点不同。没有什么真正有帮助的，即使把它放在一个批处理(有时)也不能提高性能。在这种情况下，尝试禁用自动提交，并在您完成后手动提交(警告！当多个连接同时写入时，可能会与这些操作发生冲突)

// connect(), yourList and compiledQuery you have to implement/define beforehand
try (Connection conn = connect()) {
conn.setAutoCommit(false);
preparedStatement pstmt = conn.prepareStatement(compiledQuery);
for(Object o : yourList){
pstmt.setString(o.toString());
pstmt.executeUpdate();
pstmt.getGeneratedKeys(); //if you need the generated keys
}
pstmt.close();
conn.commit();


}
小开

如果使用 jdbcTemplate，则:

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;


public int[] batchInsert(List<Book> books) {


return this.jdbcTemplate.batchUpdate(
"insert into books (name, price) values(?,?)",
new BatchPreparedStatementSetter() {


public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setString(1, books.get(i).getName());
ps.setBigDecimal(2, books.get(i).getPrice());
}


public int getBatchSize() {
return books.size();
}


});
}

或者更高级的配置

  import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;


public int[][] batchInsert(List<Book> books, int batchSize) {


int[][] updateCounts = jdbcTemplate.batchUpdate(
"insert into books (name, price) values(?,?)",
books,
batchSize,
new ParameterizedPreparedStatementSetter<Book>() {
public void setValues(PreparedStatement ps, Book argument)
throws SQLException {
ps.setString(1, argument.getName());
ps.setBigDecimal(2, argument.getPrice());
}
});
return updateCounts;


}

连接到 来源


提交答案