Optimizing MyBatis Batch Insert Performance with ExecutorType.BATCH and Proper Value Chunking

In a recent project a long‑running job showed excessive CPU usage due to a MyBatis batch insert implemented with a <foreach> loop, which generated a single massive INSERT statement.

Example of the original mapper configuration:

<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 converts many individual INSERT statements into one statement with thousands of value tuples, a technique mentioned in MySQL documentation to reduce round‑trips.

However, when the table has many columns (20+) and the batch contains thousands of rows (5000+), the generated SQL becomes extremely long, causing the database to spend a lot of time parsing the statement and mapping placeholders, which can take minutes.

Experts advise not to combine all rows into a single statement; instead, split the batch into smaller chunks (e.g., 20‑50 rows per INSERT) to avoid hitting parameter limits and to keep parsing time reasonable.

The root cause is the default MyBatis executor type ExecutorType.SIMPLE , which creates a new PreparedStatement for each execution and cannot cache statements that contain a <foreach> element. Switching to ExecutorType.BATCH prepares the statement once and reuses it for each row.

Example using MyBatis batch executor:

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

Equivalent JDBC batch code:

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 MyBatis bulk inserts prefer the batch executor or, if you must use the <foreach> approach, limit each INSERT to roughly 20‑50 rows to achieve optimal performance.