如何使用executeBatch提高性能？ [英] How to improve performance with executeBatch?

问题描述

我正在编写用于在表
中插入45000条记录的Java代码，我正在使用以下代码：

I am writing Java code for inserting 45000 records into the table and I'm using the following code:

//create Db Connection
List<String> sqlInsertQueries = getListOfInsertsQueries();
for (String currentsql : sqlInsertQueries)
{
  stmt.addBatch(currentsql);
}
stmt.executeBatch();
conn.commit();

此代码非常慢，需要将近5分钟才能完成。

This code is very slow and it takes almost 5 minutes to be finished.

有什么想法让它更快地运作吗？

Is there any idea how to make it work faster?

推荐答案

你应该确保auto commit为false，并使用预准备语句。
预备语句可用于插入和更新。

You should make sure that auto commit is false, and use prepared statements. Prepared statements can be used for insert and update.

connection con.setAutoCommit(false);  
PreparedStatement prepStmt = con.prepareStatement("INSERT INTO table (val1,val2) VALUES (?,?)");

for all values:
  prepStmt.setString(1,val1);
  prepStmt.setString(2,val2);
  prepStmt.addBatch();    

stmt.executeBatch(); 
conn.commit(); 

这篇关于如何使用executeBatch提高性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！