JDBC Batch vs. Multi-Row Inserts

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.

long start = System.currentTimeMillis();
StringBuilder build = new StringBuilder("insert into insert_values (foo, bar, baz) values ");
for (int i = 0; i < 1000; i++) {
  build.append("(?, ?, ?)");
  if (i < 999) {
    build.append(", ");
  } else {
    build.append(";");
  }
}

Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306/batch_vs_insert_test", "dev", "dev");
c.setAutoCommit(false);
PreparedStatement ps = c.prepareStatement(build.toString());
for (int i = 0; i < 3000; i++) {
  ps.setString(i + 1, "value" + i);
}

int result = ps.executeUpdate();
c.commit();

long end = System.currentTimeMillis();
System.out.println("Insert time was " + (end - start));

ps.close();
c.close();

Here is the code for the JDBC batch:

long start = System.currentTimeMillis();
Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306/batch_vs_insert_test", "dev", "dev");
c.setAutoCommit(false);
PreparedStatement ps = c.prepareStatement("insert into insert_values (foo, bar, baz) values (?, ?, ?);");
for (int i = 0; i < 3000; i++) {
  ps.setString((i % 3) + 1, "value" + i);
  if ((i + 1) % 3 == 0) {
    ps.addBatch();
  }
}

int[] results = ps.executeBatch();
c.commit();

long end = System.currentTimeMillis();
System.out.println("Batch time was " + (end - start));

ps.close();
c.close();

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.

11 thoughts on “JDBC Batch vs. Multi-Row Inserts

    1. 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.

      Like

  1. 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

    Like

  2. 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.

    Like

    1. 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.

      Like

  3. Can you please provide example generic to handle all objects with multi row insert.
    I am facing same issue for million rows batch processing is taking time. My DB version 8.2 so i cannot use reWriteBatchedInserts because its available in 9.4 version of postgres DB.

    Like

    1. Hi Sachin,

      I’m not sure what you mean by “handle all objects”. If you can post some sample code that is causing issues, I can probably help you tune it. We’ve done a lot of work around massive insert volumes.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s