Why MyBatis foreach Batch Inserts Stall and How to Speed Them Up with ExecutorType.BATCH

In a production project a long‑running job showed high CPU usage; profiling revealed that most of the time was spent on a MyBatis <foreach> based batch insert. The original mapper used a single <insert> element that generated one massive SQL statement with thousands of VALUES rows.

The author explains that this approach is not truly batch because the database receives one gigantic statement, which leads to several drawbacks:

Databases such as Oracle do not support such a statement.

MySQL (and others) have a default limit of about 2000 parameters per statement; exceeding it can cause stack errors.

The statement must be parsed and a placeholder‑to‑parameter mapping built on every execution, which becomes increasingly costly as the number of placeholders grows exponentially.

To avoid these issues the author recommends moving the iteration out of the XML and using a Java loop with ExecutorType.BATCH. The essential code looks like:

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

With ExecutorType.BATCH the prepared statement is created once and reused for each record, unlike the default ExecutorType.SIMPLE which creates a new PreparedStatement for every execution. Because the <foreach> element prevents MyBatis from caching the statement, each call re‑evaluates the foreach part and rebuilds the parameter mapping, adding significant overhead.

Internally, it still generates the same single insert statement with many placeholders as the JDBC code above. MyBatis can cache PreparedStatement , but the presence of <foreach /> makes the statement vary with parameters, so caching is disabled.

Experiments showed that using the batch executor reduced the total insert time from 14 minutes to less than 2 seconds for the same data set (5000+ rows). The author also notes that if one insists on using <foreach>, the number of rows per statement should be limited to 20‑50 to stay near the performance curve’s optimum.

MyBatis documentation actually recommends a different batch‑insert technique based on dynamic SQL. The example from the docs is:

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 approach also relies on ExecutorType.BATCH and achieves the same performance gains as the manual loop.

In summary, for MyBatis bulk inserts:

Prefer ExecutorType.BATCH with a Java loop or the MyBatis dynamic‑SQL batch API.

If <foreach> must be used, keep each batch size between 20 and 50 rows.

Avoid single giant INSERT statements because they hit parameter limits, cannot be cached, and cause costly parsing.

References include MySQL’s insert‑optimization guide, several StackOverflow discussions, and the official MyBatis dynamic‑SQL documentation.