How Can I Optimize JDBC Batch Inserts for Maximum MySQL Performance?

Optimizing JDBC Batch Insert Performance

Inserting large volumes of data into a MySQL database requires efficient batch processing to ensure optimal performance. While batch inserting 1 million records at a time may seem reasonable, there are techniques to further optimize this process.

Understanding the Issue

The code provided uses a prepared statement to insert values into a table, but it does not leverage two key properties:

• useServerPrepStmts: When set to false, MySQL will prepare the statement on the server instead of the client, reducing network overhead.
• rewriteBatchedStatements: When set to true, MySQL will rewrite batched statements to use a more efficient multi-statement execution method.

Optimizing the Connection

To enable these optimizations, set both properties in the connection URL:

Connection c = DriverManager.getConnection("jdbc:mysql://host:3306/db?useServerPrepStmts=false&rewriteBatchedStatements=true", "username", "password");

Modified Code

With these optimizations in place, the updated code becomes:

try {
    // Disable auto-commit
    c.setAutoCommit(false);

    // Create a prepared statement
    String sql = "INSERT INTO mytable (xxx), VALUES(?)";
    PreparedStatement pstmt = c.prepareStatement(sql);

    Object[] vals=set.toArray();
    for (int i=0; i<vals.length; i++) {
        pstmt.setString(1, vals[i].toString());
        pstmt.addBatch();
    }

    // Execute the batch
    int [] updateCounts = pstmt.executeBatch();
    System.out.append("inserted "+updateCounts.length);
} catch (SQLException e) {
    e.printStackTrace();
}

Conclusion

By setting the "useServerPrepStmts" and "rewriteBatchedStatements" properties, the batch insert process can be significantly optimized, reducing the elapsed time required to insert large volumes of data.

The above is the detailed content of How Can I Optimize JDBC Batch Inserts for Maximum MySQL Performance?. For more information, please follow other related articles on the PHP Chinese website!