Optimizing MyBatis Batch Inserts with ExecutorType.BATCH and foreach Size Limits

In a recent project a long‑running job showed excessive CPU usage because the bulk insert was implemented with a MyBatis <foreach> loop, generating a single massive SQL statement.

The original MyBatis mapper looked 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>

When the list contained thousands of records, the generated INSERT statement contained thousands of VALUES clauses, causing the JDBC driver to create an extremely long PreparedStatement. The parsing of placeholders and the mapping of parameters grew exponentially, leading to execution times of up to 14 minutes.

MySQL documentation recommends combining many small inserts into a single large statement, but the statement becomes too large for the driver and for the database’s internal limits (around 2000 parameters per statement). Moreover, MyBatis cannot cache a statement that contains a <foreach> element because the SQL string changes with each execution.

Two practical solutions are presented:

Use MyBatis ExecutorType.BATCH so that each row is inserted with a prepared statement that is reused, flushing the batch at the end:

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

This approach reduces the parsing overhead and brings the total insert time down to under 2 seconds for the same data set.

Alternatively, limit the number of rows per <foreach> batch to 20‑50 rows, which empirical testing shows yields the best trade‑off between statement size and performance.

MyBatis also provides a dynamic‑SQL batch insert API (see MyBatis‑Dynamic‑SQL documentation). An example using ExecutorType.BATCH with the dynamic 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().stream().forEach(mapper::insert);
    session.commit();
} finally {
    session.close();
}

For pure JDBC, enabling rewriteBatchedStatements=true and using PreparedStatement.addBatch() achieves similar performance:

Connection connection = DriverManager.getConnection(
    "jdbc:mysql://127.0.0.1:3306/mydb?useUnicode=true&characterEncoding=UTF-8&useServerPrepStmts=false&rewriteBatchedStatements=true",
    "root", "root");
connection.setAutoCommit(false);
PreparedStatement ps = connection.prepareStatement("insert into tb_user (name) values(?)");
for (int i = 0; i < stuNum; i++) {
    ps.setString(1, name);
    ps.addBatch();
}
ps.executeBatch();
connection.commit();
connection.close();

In summary, for MyBatis bulk inserts, prefer ExecutorType.BATCH or the dynamic‑SQL batch API; if <foreach> must be used, keep each batch to 20‑50 rows to avoid the exponential parsing cost.