fast way to insert into database using java

Question

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

It will likely be faster to execute it in **smaller** batches. — Mark Rotteveel, Apr 27 '17 at 13:17
in [this question](http://stackoverflow.com/questions/3784197/efficient-way-to-do-batch-inserts-with-jdbc), the conclusion reached was that the way you're doing it is the fastest, as the statement only needs to be parsed once. It's for Oracle instead of MySql, but I think the same consideration applies here. — Roc Aràjol, Apr 27 '17 at 13:22
OK, I will try creating smaller batches and execute, to see if it makes any impact on time taken to execute. Thank you — Gaurav Raj Ghimire, Apr 27 '17 at 13:37
Since you're using MySQL you may want to use `rewriteBatchedStatements=true` as discussed in [this answer](http://stackoverflow.com/a/26313288/2144390). — Gord Thompson, Apr 27 '17 at 13:45
wow! that one statement reduced the time taken to insert 100k values from around 20min+ to just 5 seconds. That is amazing. Thank you very much Gord Thompson. — Gaurav Raj Ghimire, Apr 27 '17 at 14:18

score 1 · Accepted Answer · edited May 23 '17 at 12:26

1

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.

edited May 23 '17 at 12:26

Community

1
1

answered Apr 27 '17 at 23:34

Gord Thompson

116,920
32
215
418

score 0 · Answer 2 · answered Apr 30 '17 at 00:40

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.

fast way to insert into database using java

2 Answers2