Optimizing MyBatis Batch Inserts: Reducing CPU Usage and Improving Performance

In a recent project a long‑running job consumed excessive CPU because the bulk of the time was spent on batch inserting data with MyBatis using a <foreach> loop.

The typical 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>

The performance gain comes from converting many single‑row INSERT statements into a single multi‑row INSERT, e.g.:

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

MySQL documentation confirms that grouping many small operations into one large statement can reduce the number of index updates and consistency checks, but it also warns that the statement should not become too large; otherwise it may hit parameter limits or cause parsing overhead.

When using <foreach> with thousands of values (e.g., 5000+), the generated PreparedStatement becomes extremely long, causing exponential growth in parsing time because MyBatis must evaluate the foreach part and build parameter mappings for every execution.

Practical experience shows that limiting each INSERT to about 20‑50 rows keeps the execution time acceptable; larger batches lead to unacceptable delays.

MyBatis itself recommends a different batch‑insert approach by setting the session executor to ExecutorType.BATCH and issuing many small inserts, 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().forEach(mapper::insert);
    session.commit();
} finally {
    session.close();
}

The same idea can be applied directly with JDBC by enabling rewriteBatchedStatements=true and using addBatch():

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

Experiments show that using ExecutorType.BATCH reduces the total insertion time to under two seconds, while a foreach‑based approach with thousands of rows can take many minutes.

In summary, for MyBatis batch inserts prefer the ExecutorType.BATCH method; if you must use <foreach>, keep each batch size between 20 and 50 rows.