Boost MyBatis Batch Inserts: Why foreach Slows You Down and How to Fix It

Recently a job in a project suffered high CPU usage because the MyBatis foreach batch insert generated an extremely long SQL statement, causing severe performance issues.

<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 traditional approach of issuing many separate INSERT statements can be replaced by a single INSERT with multiple VALUES, which reduces round‑trips and defers index updates.

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

MySQL documentation confirms that combining many small operations into one large statement improves insert speed, but the statement size must be kept reasonable.

When the foreach generates 5000+ VALUES, the resulting PreparedStatement becomes huge, leading to exponential growth in parsing time and memory consumption.

Therefore, if you must use foreach, keep each INSERT statement to about 20‑50 rows to stay near the performance curve’s bottom.

The recommended MyBatis way is to use a batch executor:

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

Or using MyBatis Dynamic SQL’s batch insert support:

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

A plain JDBC batch example also works:

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 reduces the total insert time to under 2 seconds for thousands of rows.

In summary, for MyBatis bulk inserts, prefer ExecutorType.BATCH or limit each batch to 20‑50 rows; avoid large foreach‑generated statements.