Optimizing MyBatis Batch Inserts with ExecutorType.BATCH and Controlled foreach Values

In a recent project a long‑running job showed high CPU usage because the bulk insert was implemented with a MyBatis <foreach> loop that generated a massive single INSERT statement.

The original mapper XML 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>

Traditional MySQL INSERT statements execute one row per statement, but they can be rewritten as a multi‑value INSERT to reduce round‑trips, for example:

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

MySQL documentation confirms that combining many small inserts into a single large statement can improve speed, yet when the <foreach> generates 5,000+ value tuples the single statement becomes huge, taking up to 14 minutes to execute.

Of course don't combine ALL of them, if the amount is HUGE. Say you have 1000 rows you need to insert, then don't do it one at a time. You shouldn't equally try to have all 1000 rows in a single query. Instead break it into smaller sizes.

The reason is that MyBatis treats the <foreach> as a single (potentially gigantic) SQL statement, which prevents the JDBC driver from using true batch execution and forces the framework to parse and map a massive number of placeholders on every call.

To avoid this, the recommended approach is to use a Java for loop together with ExecutorType.BATCH:

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 executed repeatedly, unlike the default ExecutorType.SIMPLE which creates a new PreparedStatement for each row.

Because the XML <foreach> element cannot be cached, MyBatis must re‑evaluate and re‑parse the SQL on every execution, which adds considerable overhead, especially when the generated statement contains thousands of placeholders.

If you must keep the <foreach> approach, limit the number of VALUES per INSERT to roughly 20‑50 rows; this range yields the lowest execution time according to empirical tests.

MyBatis also provides a dedicated batch‑insert API via MyBatis Dynamic SQL, for example:

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();
}

The same idea can be expressed directly with plain JDBC batch statements:

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();

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

In summary, for high‑performance MyBatis bulk inserts you should prefer the batch executor or, if you stay with <foreach>, keep each INSERT statement to about 20‑50 rows to avoid the exponential parsing cost.