Optimizing MyBatis Batch Insert Performance with ExecutorType.BATCH and Foreach Limits

In a recent project a long‑running job caused high CPU usage because bulk data was inserted into MySQL using a MyBatis <foreach> loop, which generated a single massive INSERT statement.

The original mapper configuration looks like this:

<insert id="batchInsert" parameterType="java.util.List">
    insert into USER (id, name) values
    <foreach collection="list" item="model" index="index" separator=",">
        (#{model.id}, #{model.name})
    </foreach>
</insert>

This approach concatenates thousands of value tuples into one statement, which MySQL can handle but leads to a very long SQL string, many placeholders, and costly parsing and parameter‑mapping on each execution.

MySQL documentation recommends combining many small INSERTs into a larger one, yet practical tests show that when the column count exceeds 20 and the row count exceeds 5,000, the execution time can reach 14 minutes.

"If you have 1,000 rows to insert, don’t do it one at a time, but also don’t put all 1,000 rows in a single query. Break it into smaller sizes."

The root cause is that the <foreach> construct prevents MyBatis from caching the PreparedStatement, so the statement is re‑parsed and the placeholder‑to‑parameter mapping is rebuilt for every execution.

A better solution is to use MyBatis’s batch executor:

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

Unlike the default ExecutorType.SIMPLE, the batch executor prepares the statement once and reuses it for each record, dramatically reducing overhead.

When the <foreach> method must be used, the number of rows per INSERT should be limited to about 20‑50 to keep the statement size manageable.

Another recommended approach is MyBatis Dynamic SQL’s batch insert support, which also relies on ExecutorType.BATCH:

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().stream().forEach(mapper::insert);
    session.commit();
} finally {
    session.close();
}

Testing shows that using ExecutorType.BATCH reduces the total insertion time to under 2 seconds for the same data set.

In summary, for high‑volume MyBatis inserts, prefer the batch executor or limit <foreach> batch sizes to 20‑50 rows per statement to achieve optimal performance.