CODEWITHSUNDEEP
javamysqldatabaseperformancejdbc
Gaurav Raj Ghimire
Gaurav Raj Ghimire

Reputation: 9

fast way to insert into database using java

I was curious about how fast we can execute MySQL query through a loop in Java and well it is taking extremely long time with the code I wrote. I am sure there is a faster way to do it. Here is the piece of code that is executing the query.

PreparedStatement ps=connection.prepareStatement("INSERT INTO books.author VALUES (?,?,?)");
for (int i=0;i<100000;i++)
{
       ps.setString(1,test[i][0]);
       ps.setString(2,test[i][1]);
       ps.setString(3,test[i][2]);
       ps.addBatch();
}
int []p=ps.executeBatch();

Any help would be much appreciated. Thank you

Upvotes: 0

Views: 2164

Answers (2)

Rick James
Rick James

Reputation: 142298

There are a number of other things that kick in when you have a huge batch. So going "too" big will slow down the rows/second. This depends on a few settings, the specific table schema, and other things. I have see a too-big batch run twice as slow as a more civilized batch.

Think of the overhead of parsing the query as being equivalent to inserting an extra 10 rows. Based on that Rule of Thumb, a batch insert of 1000 rows is within 1% of the theoretical maximum speed.

It may be faster to create a file with all the rows and do LOAD DATA. But, again, this depends on various settings, etc.

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123549

Your basic approach is correct. Since you are using MySQL you may want to add rewriteBatchedStatements=true to your connection URL as discussed in this answer. It has been known to significantly speed up batch INSERT operations to MySQL databases.

Upvotes: 1

Related Questions