How to improve performance with executeBatch?

Question

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

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

Is there any idea how to make it work faster?

Answer 1

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