How to Supercharge MyBatis Batch Inserts: From 14‑Minute Loads to Sub‑2‑Second Performance

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

<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 idea behind this method is to replace many individual INSERT INTO ... VALUES (...) statements with one statement that contains multiple value tuples, which MySQL documentation confirms can dramatically improve insert speed by sending many rows in a single round‑trip.

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

However, when the table has many columns (20+) and the batch contains thousands of rows (5000+), the generated statement becomes extremely long, leading to a 14‑minute execution time. The MyBatis <foreach> approach does not truly batch; it creates a single gigantic SQL string, which has several drawbacks:

Some databases (e.g., Oracle) do not support such large statements.

The default parameter limit (around 2000 parameters per statement) can be exceeded, causing stack errors.

Because the statement is rebuilt for every execution, MyBatis cannot cache the PreparedStatement. The parsing of the huge SQL string and the mapping of countless placeholders become costly operations.

A better solution is to use MyBatis’s batch executor. By opening a session with ExecutorType.BATCH and inserting each record in a Java loop, the same SQL is prepared once and executed repeatedly, similar to JDBC batch processing.

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

Compared with the default ExecutorType.SIMPLE, the batch executor prepares the statement only once, avoiding repeated parsing and placeholder mapping.

If you must keep the <foreach> style, limit the number of rows per INSERT to about 20‑50, which yields the best performance according to empirical tests.

MyBatis also provides a dedicated batch‑insert API (MyBatis Dynamic SQL) that builds a series of small INSERT statements and executes them with a batch executor:

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")
        .build()
        .render(RenderingStrategy.MYBATIS3);
    batchInsert.insertStatements().forEach(mapper::insert);
    session.commit();
} finally {
    session.close();
}

The equivalent JDBC batch code is:

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 two seconds, while a foreach‑based insert should keep each batch to 20‑50 rows for acceptable performance.