如何使用executeBatch来提高性能?

时间:2022-02-01 06:38:49

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

我正在编写Java代码将45000条记录插入到表中,我使用以下代码:

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

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

这段代码非常慢,大约需要5分钟才能完成。

Is there any idea how to make it work faster?

有没有办法让它工作得更快?

1 个解决方案

#1


16  

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(); 

#1


16  

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();