I have a piece of code that executes about 500,000 inserts on a database. It's now done in a loop calling PreparedStatement's executeUpdate at each iteration. Would it be faster to add all 500,000 inserts to a batch and and call executeBatch just once?
5
- 1What does "better" mean? Simpler? Faster? Easier to maintain? Less use of network? Easier to rollback in the event of a problem?S.Lott– S.Lott2009-08-17 18:06:51 +00:00Commented Aug 17, 2009 at 18:06
- 1@S.Lott: it's pretty safe to assume "better" means "faster" in 99% of questions about SQL.Bill Karwin– Bill Karwin2009-08-17 18:22:04 +00:00Commented Aug 17, 2009 at 18:22
- @Bill Karwin: Interesting observation. How do you know that? Is it true for this question? How do you know that?S.Lott– S.Lott2009-08-17 18:32:47 +00:00Commented Aug 17, 2009 at 18:32
- @S.Lott: Of course, in general "better" is not equal "faster". But other assumptions for "better" in this context are not really obvious. Faster is obvious when talking about JDBC/batch/prepared statement and 500,000 inserts. By keeping questions concise and not explaining obvious facts we do a great deal of favor to other users.topchef– topchef2009-08-17 18:56:17 +00:00Commented Aug 17, 2009 at 18:56
- It was true for my question. Sorry i didn't make myself clear enough. I meant faster!Guilherme– Guilherme2009-08-17 20:28:24 +00:00Commented Aug 17, 2009 at 20:28
Add a comment |
3 Answers
Using PreparedStatement in combination with batch update facility yields most efficient results (from Sun JDBC doc):
// turn off autocommit con.setAutoCommit(false); PreparedStatement stmt = con.prepareStatement( "INSERT INTO employees VALUES (?, ?)"); stmt.setInt(1, 2000); stmt.setString(2, "Kelly Kaufmann"); stmt.addBatch(); stmt.setInt(1, 3000); stmt.setString(2, "Bill Barnes"); stmt.addBatch(); // submit the batch for execution int[] updateCounts = stmt.executeBatch(); Comments
Yes, it will be much faster. make sure you turn autoCommit off first, otherwise you get no performance benefit.
Comments
500.000 is way too much to add at once. Remember those records are kept in memory and sent at once.Add them in batches of a few thousands, I didn't notice any improvement between 1000 and 10000 rows in a batch(with MySQL) but I presume some other factors counts.
1 Comment
topchef
it could quite feasible for small example with prepared statement. but it really depends, i agree...