Boost MyBatis Batch Inserts: Why foreach Slows Down and How to Fix It

Problem Overview

In a project a long‑running job showed high CPU usage because the bulk insert was implemented with a MyBatis <foreach> loop, generating a single massive INSERT statement. When inserting many columns (20+) and thousands of rows (5000+), the operation took up to 14 minutes.

Traditional vs. Combined INSERT

Typical code executes many separate INSERT statements:

INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
...

MySQL documentation suggests combining them into one statement with multiple value tuples:

INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2"),
                                 ("data1", "data2"),
                                 ("data1", "data2"),
                                 ("data1", "data2");

While this reduces round‑trips, the generated SQL becomes extremely long, causing parsing and placeholder‑mapping overhead.

Why <foreach> Is Not a Real Batch

The <foreach> construct produces a single giant SQL statement, which hits database limits (e.g., ~2000 parameters per statement) and forces MyBatis to re‑parse and re‑map placeholders on every execution. The default ExecutorType.SIMPLE creates a new PreparedStatement for each call, and MyBatis cannot cache statements containing <foreach>.

Recommended Solution

Do not perform the iteration in the MyBatis XML. Instead, use a Java loop with ExecutorType.BATCH so the statement is prepared once and executed repeatedly:

SqlSession session = sessionFactory.openSession(ExecutorType.BATCH);
for (Model model : list) {
    session.insert("insertStatement", model);
}
session.flushStatements();

This approach avoids the huge SQL string and dramatically reduces parsing cost. Experiments show insertion time dropping from minutes to under 2 seconds.

Batch Size Guidance

Even with batch execution, inserting an excessive number of rows in a single batch is unwise. A practical batch size of 20–50 rows per statement balances performance and avoids hitting parameter limits.

Alternative: MyBatis Dynamic SQL Batch Insert

MyBatis’ dynamic‑SQL module provides a dedicated batch‑insert API:

SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
try {
    SimpleTableMapper mapper = session.getMapper(SimpleTableMapper.class);
    List<SimpleTableRecord> records = getRecordsToInsert();
    BatchInsert<SimpleTableRecord> batchInsert = insert(records)
        .into(simpleTable)
        .map(id).toProperty("id")
        .map(firstName).toProperty("firstName")
        .map(lastName).toProperty("lastName")
        .map(birthDate).toProperty("birthDate")
        .map(employed).toProperty("employed")
        .map(occupation).toProperty("occupation")
        .build()
        .render(RenderingStrategy.MYBATIS3);
    batchInsert.insertStatements().forEach(mapper::insert);
    session.commit();
} finally {
    session.close();
}

This method also relies on ExecutorType.BATCH and yields similar performance gains.

Conclusion

For MyBatis bulk inserts, prefer using ExecutorType.BATCH with a reasonable batch size (20‑50 rows) or adopt MyBatis Dynamic SQL’s batch‑insert support. Avoid the <foreach> pattern that creates a single massive INSERT statement, as it leads to heavy parsing overhead and poor performance.