Can JDBC Batch Insertion Speed Up MySQL? 100M Row Performance Test

Background

When optimizing MySQL query performance, small test data (e.g., 100,000 rows) is insufficient for reliable measurements. To obtain meaningful results, the author generated random person records (ID, name, gender, age, email, phone, address) and inserted them into MySQL at a scale of hundreds of millions of rows.

Sample data set (33.3 million rows):

Test Strategies

MyBatis lightweight framework insertion (no transaction)

JDBC direct processing (with and without transaction)

JDBC batch processing (with and without transaction)

Results Overview

MyBatis lightweight insert → JDBC direct processing → JDBC batch processing.

JDBC batch processing proved the most efficient.

Strategy 1: MyBatis Lightweight Insertion (No Transaction)

MyBatis is a lightweight ORM framework, but when inserting large volumes without a transaction, performance is poor because each insert triggers an individual SQL execution.

private long begin = 33112001; // start ID
private long end = begin + 100000; // batch size
private String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8";
private String user = "root";
private String password = "0203";

@Test
public void insertBigData2() {
    ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
    PersonMapper pMapper = (PersonMapper) context.getBean("personMapper");
    Person person = new Person();
    long bTime = System.currentTimeMillis();
    for (int i = 0; i < 5000000; i++) {
        person.setId(i);
        person.setName(RandomValue.getChineseName());
        person.setSex(RandomValue.name_sex);
        person.setAge(RandomValue.getNum(1, 100));
        person.setEmail(RandomValue.getEmail(4, 15));
        person.setTel(RandomValue.getTel());
        person.setAddress(RandomValue.getRoad());
        pMapper.insert(person);
        begin++;
    }
    long eTime = System.currentTimeMillis();
    System.out.println("Inserted 5M rows in: " + (eTime - bTime));
}

The test stopped after inserting ~520,000 rows, taking about 7–9 minutes.

MyBatis lightweight insertion – 10,000 rows took 28.6 seconds.

Strategy 2: JDBC Direct Processing

2.1 With and Without Transaction

The experiment compares JDBC inserts with transaction enabled and disabled.

// Transactional JDBC insert
Connection conn = null;
PreparedStatement pstm = null;
try {
    Class.forName("com.mysql.jdbc.Driver");
    conn = DriverManager.getConnection(url, user, password);
    conn.setAutoCommit(false);
    String sql = "INSERT INTO person VALUES (?,?,?,?,?,?,?)";
    pstm = conn.prepareStatement(sql);
    long bTime1 = System.currentTimeMillis();
    for (int i = 0; i < 10; i++) { // each loop inserts 10,000 rows
        long bTime = System.currentTimeMillis();
        while (begin < end) {
            pstm.setLong(1, begin);
            pstm.setString(2, RandomValue.getChineseName());
            pstm.setString(3, RandomValue.name_sex);
            pstm.setInt(4, RandomValue.getNum(1, 100));
            pstm.setString(5, RandomValue.getEmail(4, 15));
            pstm.setString(6, RandomValue.getTel());
            pstm.setString(7, RandomValue.getRoad());
            pstm.execute();
            begin++;
        }
        conn.commit();
        end += 10000;
        long eTime = System.currentTimeMillis();
        System.out.println("Inserted 10K rows in: " + (eTime - bTime));
    }
    long eTime1 = System.currentTimeMillis();
    System.out.println("Inserted 100K rows total time: " + (eTime1 - bTime1));
} catch (SQLException | ClassNotFoundException e) {
    e.printStackTrace();
}

Results without transaction (average per 10K rows): ~21.2 seconds.

Average without transaction – 21.2 s per 10,000 rows.

Results with transaction (average per 10K rows): ~3.9 seconds.

Average with transaction – 3.9 s per 10,000 rows.

Strategy 3: JDBC Batch Processing

2.3 With and Without Transaction

Key points for batch processing:

Enable rewriteBatchedStatements=true in the JDBC URL.

Prepare the PreparedStatement once outside the loop.

String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8";
// ...
PreparedStatement pstm = conn.prepareStatement(sql);
for (int i = 0; i < 10; i++) { // each iteration inserts 100,000 rows
    long bTime = System.currentTimeMillis();
    while (begin < end) {
        pstm.setLong(1, begin);
        pstm.setString(2, RandomValue.getChineseName());
        pstm.setString(3, RandomValue.name_sex);
        pstm.setInt(4, RandomValue.getNum(1, 100));
        pstm.setString(5, RandomValue.getEmail(4, 15));
        pstm.setString(6, RandomValue.getTel());
        pstm.setString(7, RandomValue.getRoad());
        pstm.addBatch();
        begin++;
    }
    pstm.executeBatch();
    conn.commit(); // optional, depending on transaction mode
    end += 100000;
    long eTime = System.currentTimeMillis();
    System.out.println("Inserted 100K rows in: " + (eTime - bTime));
}
long eTime1 = System.currentTimeMillis();
System.out.println("Inserted 1M rows total time: " + (eTime1 - bTime1));

Results without transaction: average 2.1 s per 100,000 rows.

JDBC batch (no transaction) – 2.1 s per 100K rows.

Results with transaction: average 1.9 s per 100,000 rows.

JDBC batch (with transaction) – 1.9 s per 100K rows.

Conclusion

MyBatis insertion is the slowest; enabling transactions narrows the gap but still lags by an order of magnitude.

JDBC direct processing benefits significantly from transactions, reducing time by roughly fivefold.

JDBC batch processing combined with transactions delivers the best performance, inserting 100 million rows in about 174 seconds (≈2.9 minutes).

Final recommendation: for massive single‑row insert workloads, use JDBC batch processing together with transaction management.