Jun 212011
 

I recently had a requirement to insert a few hudred rows into a relational database ever couple of seconds. Generally this could be accomplished during the request, but I didn’t want to introduce issues if there were spikes. Therefore, I figured I would cache the data and then write it out in a background thread every few seconds. This would also increase my response time during requests.

I wasn’t sure whether or not JDBC batch or using a single insert statement that inserted multiple rows would be faster. Therefore, I setup a little test to see which was going to work better. First some code:

Here is the code for a single insert statement that inserts multiple rows.

Here is the code for the JDBC batch:

The average time over 10 iterations where roughly as follows:

JDBC Batch: ~100 milliseconds
Single Insert: ~10 milliseconds

It looks like the single insert statement wins by a large margin.

UPDATE: This method yields very similar results on both MySQL and PostgreSQL using the latest drivers from each organization.

  8 Responses to “JDBC Batch vs. Multi-Row Inserts”

  1. Hi Brian, with 3000 rows I can not see the performance. 3.000.000 rows is more appropriated no ?

    • Depending on your setup and tests, you should see a performance bump with as little as 3,000 rows. However, if you really want to see the difference, insert 3,000 rows using a single insert statement and a batch statement 100,000 times each. That should show you the differences nicely. This would require an outer loop over my code, but would definitely reveal which is faster.

  2. Hi Brian, thanks for interesting post. Can you please update the post with which database and jdbc driver version you used during the test? Because recently I found out that there are quite big differences in jdbc drivers where it comes to performance… thanks

  3. I am working on writing data into MySQL, I used
    String URL =”jdbc:mysql://localhost/database_name?rewriteBatchedStatements=true”

    that is I added “?rewriteBatchedStatements=true” at the end, then the batch insert speeds up, and performs much faster than the individual insert.

    • I did a bit of research and that option for the driver appears to be doing the same thing as a bulk insert. It caches the inserts and rewrites them into a single statement. I would expect that this would be much faster than individual inserts, faster than batch inserts, but slower than a single bulk insert statement from Java code.

    • This worked like a charm. I got a speed up from 100 to 12 seconds!

  4. Hi Brian, You have a beautiful programming mind :) I am glad I came here and had an eyeful of your blog :) Keep updating .
    Thanks

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

(required)

(required)

This site uses Akismet to reduce spam. Learn how your comment data is processed.