Performant Batch Inserts Using JDBC

gowebing

The Problem

If you've ever tried to insert data via a JDBC connection — say to a MySQL database — in bulk, then you may have had to sit through several minutes or even hours for hundreds of thousands — or even millions —of records to finish being added to the database. Even without the overhead of transactions, this wait time is comparable.

So what can we do to improve the performance of these bulk inserts?  Interestingly, there is something small that can be adjusted that can have a very large impact.

Let's consider the following example:

  Connection connection = null;

  // connect to the database here and set the "connection" variable

  try {
    String query = "INSERT INTO table1 (field1, field2) VALUES (?, ?)";

    PreparedStatement preparedStatement = connection.prepareStatement(query);

    for (int i = 0; i < 1000000) {
      preparedStatement.setString(1, "value" + i);
      preparedStatement.setInt(2, i);

      preparedStatement.addBatch();
    }

    preparedStatement.executeBatch();
  } catch (SQLException e1) {
    // handle the exception
  }


The above code simply creates a batch insert using a PreparedStatement from the java.sql library.

Nothing surprising there.

However, let's take a look at how the connection to the database is established:

try {
  String connectionString = "jdbc:mysql://localhost:3306/my_db"; // be sure to add any other parameters needed to ensure an authorized connection

  Class.forName("com.mysql.cj.jdbc.Driver"); // ...or your favorite JDBC driver

  this.connection = DriverManager.getConnection(connectionString);
} catch (ClassNotFoundException | SQLException e) {
  // deal with the exceptioun
}


With the above code segments, we have a fairly typical interaction with a MySQL database via a JDBC driver.

However, most who run this code will notice how painfully slow the overall process is.

A Possible Solution

So what can be done?

One simple setting in the connection string may make a world of difference.

If we add the following to our connection string rewriteBatchedStatements=true, like the following:

  String connectionString = "jdbc:mysql://localhost:3306/my_db?rewriteBatchedStatements=true"


(...of course, being sure to augment the connection string with any other parameters, such as those to ensure an authenticated connection...)

And then, re-run the above code segments; you should notice a substantial increase in performance!

How Does It Work?

Quite simply, the connection string setting we added tells the database to take any series of individual INSERT statements and convert them to a single multi-value INSERT statement, e.g.:

INSERT INTO table1 (field1, field2) VALUES ("string1", 1);
INSERT INTO table1 (field1, field2) VALUES ("string2", 2);
INSERT INTO table1 (field1, field2) VALUES ("string3", 3);
INSERT INTO table1 (field1, field2) VALUES ("string4", 4);


becomes...

INSERT INTO table1 (field1, field2) VALUES  ("string1", 1), 
("string2", 2), 
                                            ("string3", 3), 
                                            ("string4", 4);


Give it a shot the next time you run into this situation! Let me know how it goes in the comments below.

 

 

 

 

Top