Speed Up MyBatis Batch Inserts: Avoid foreach Pitfalls with ExecutorType.BATCH

Recently a job in a project showed high CPU usage; investigation revealed that most of the time was spent on batch inserting data with MyBatis using a foreach loop.

<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 is to replace many single‑row INSERT statements with a single multi‑row INSERT, which MySQL can execute more efficiently by sending many rows in one connection and delaying index updates.

However, when the table has many columns (20+) and the batch contains thousands of rows (5000+), the single massive INSERT can take up to 14 minutes. As noted in MySQL documentation and community discussions, inserting an extremely large number of rows in one statement is not advisable.

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.

Using foreach in MyBatis generates a single (potentially gigantic) SQL statement, which has several drawbacks: some databases have a limit on the number of parameters per statement (around 2000), the statement parsing and placeholder‑parameter mapping become costly, and the statement cannot be cached because it varies with the foreach content.

Insert inside Mybatis foreach is not batch, this is a single (could become giant) SQL statement and that brings drawbacks:

some database such as Oracle here does not support.
in relevant cases: there will be a large number of records to insert and the database configured limit (by default around 2000 parameters per statement) will be hit, and eventually possibly DB stack error if the statement itself become too large.
Iteration over the collection must not be done in the mybatis XML. Just execute a simple Insert statement in a Java Foreach loop. The most important thing is the session Executor type.

SqlSession session = sessionFactory.openSession(ExecutorType.BATCH);
for (Model model : list) {
    session.insert("insertStatement", model);
}
session.flushStatements();
Unlike default ExecutorType.SIMPLE, the statement will be prepared once and executed for each record to insert.

Therefore, to improve performance you can either limit each INSERT to 20‑50 rows (the sweet spot observed in tests) or use MyBatis’s 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")
            .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();
}

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

Tests showed that using ExecutorType.BATCH reduces the insertion time to less than 2 seconds for the same data set.

Conclusion: For MyBatis bulk inserts, prefer ExecutorType.BATCH; if you must use foreach, keep each batch size between 20 and 50 rows.

https://dev.mysql.com/doc/refman/5.6/en/insert-optimization.html

https://stackoverflow.com/questions/19682414/how-can-mysql-insert-millions-records-fast

https://stackoverflow.com/questions/32649759/using-foreach-to-do-batch-insert-with-mybatis/40608353

https://blog.csdn.net/wlwlwlwl015/article/details/50246717

http://blog.harawata.net/2016/04/bulk-insert-multi-row-vs-batch-using.html

https://www.red-gate.com/simple-talk/sql/performance/comparing-multiple-rows-insert-vs-single-row-insert-with-three-data-load-methods/

https://stackoverflow.com/questions/7004390/java-batch-insert-into-mysql-very-slow

http://www.mybatis.org/mybatis-dynamic-sql/docs/insert.html