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
– Mark Rotteveel, Commented Apr 27, 2017 at 13:17
in this question, 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
– Roc Aràjol, Commented Apr 27, 2017 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
– Gaurav Raj Ghimire, Commented Apr 27, 2017 at 13:37
Since you're using MySQL you may want to use rewriteBatchedStatements=true as discussed in this answer. — Gord Thompson
– Gord Thompson, Commented Apr 27, 2017 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
– Gaurav Raj Ghimire, Commented Apr 27, 2017 at 14:18

Community · Accepted Answer · 2017-05-23 12:26:12Z

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, 2017 at 12:26

CommunityBot

11 silver badge

answered Apr 27, 2017 at 23:34

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rick James · Accepted Answer · 2017-04-30 00:40:18Z

0

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.

answered Apr 30, 2017 at 0:40

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

fast way to insert into database using java

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related