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.

  16 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

  5. Thankyou for helping out, wonderful info .

  6. Ⲃut, aѕ a maatter оff
    fact, tһе shipping charges սsually prove
    tо Ье much lower аs compared
    tⲟ thee travelling аѕ աell as transportation costs faced ⅾuring offline shopping.
    Օne ⲟf tɦе main reasonss people shop offline іs fоr all tһе ցreat sales they
    sᥱᥱn іn local newspapers ߋr tҺе main. Emart iѕ a ѕub-division oof Apex Pacific, ɑ ԝorld-renowned Internet marketing company.

  7. With this elegant, three piece dining set you
    may create a contemporary styled eating space.

  8. This is a topic which is close to my heart… Best
    wishes! Where are your contact details though?

  9. I enjoy what you guys are usually up too. This sort of clever work and coverage!
    Keep up the wonderful works guys I’ve added you guys to my blogroll.

  10. You really make it seem so easy with your presentation but I find this matter to
    be actually something which I think I would never understand.
    It seems too complex and extremely broad for me.
    I am looking forward for your next post, I’ll try to get the hang of it!

  11. jesuis tres fier de etre née a st francois pabos toujours le monde aussi souriant au service avec grand cœur a matin jesuis tres heureux de voir se si beaux montage moi je sain que ses la verité felicitation pour avoir pensée a nous autre
    Kate Spade coupon http://www.katespadecoupons.com

  12. Amazing! Its genuinely awesome article, I
    have got much clear idea on the topic of from this article.

    Sttop by mmy website diana

 Leave a Reply

(required)

(required)

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="">