Boost MyBatis Batch Insert Speed: Avoid foreach Pitfalls and Use ExecutorType.BATCH

Recently a job in a project suffered high CPU usage because batch inserting data with MyBatis took a long time. The mapper configuration used a

<foreach>

loop, similar to the following demo:

<code>&lt;insert id="batchInsert" parameterType="java.util.List"&gt;
    insert into USER (id, name) values
    &lt;foreach collection="list" item="model" index="index" separator=","&gt;
        (#{model.id}, #{model.name})
    &lt;/foreach&gt;
&lt;/insert&gt;
</code>

The idea behind this method is to replace many individual

INSERT INTO `table1` ... VALUES (...);

statements with a single

INSERT

that contains multiple

VALUES

clauses, as recommended by MySQL documentation to reduce round‑trips and defer index updates.

However, when the table has many columns (20+) and the batch contains thousands of rows (5000+), the generated SQL becomes huge and the insert takes up to 14 minutes.

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.

The

<foreach>

approach creates a single massive SQL statement rather than a true batch, leading to several drawbacks:

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

The number of parameters can exceed the database limit (around 2000 by default), causing errors.

Instead of iterating in the MyBatis XML, execute a simple insert inside a Java

for

loop and set the session executor to

ExecutorType.BATCH

:

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

With the default

ExecutorType.SIMPLE

, a new

PreparedStatement

is created for each execution, and because the statement contains a

<foreach>

element it cannot be cached, causing repeated parsing and placeholder mapping that grows exponentially with the number of rows.

Internally, it still generates the same single insert statement with many placeholders as the JDBC code above. MyBatis can cache PreparedStatement , but the presence of <foreach /> prevents caching; the statement varies with parameters, so MyBatis must evaluate the foreach part and parse the SQL each time, which is costly for large statements.

Therefore, if you must use

<foreach>

, limit the number of

VALUES

per statement to about 20‑50 rows, which yields the best performance according to the curve shown below.

MyBatis documentation actually recommends a different batch‑insert method using MyBatis Dynamic SQL:

<code>SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
try {
    SimpleTableMapper mapper = session.getMapper(SimpleTableMapper.class);
    List&lt;SimpleTableRecord&gt; records = getRecordsToInsert(); // not shown

    BatchInsert&lt;SimpleTableRecord&gt; 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();
}
</code>

This approach sets the session executor to

BATCH

and executes many insert statements, similar to the plain JDBC batch example:

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

Experiments show that using

ExecutorType.BATCH

reduces the total insertion time to under 2 seconds.

Conclusion: For MyBatis batch inserts, prefer the

ExecutorType.BATCH

approach; if you must use

<foreach>

, keep each batch to roughly 20‑50 rows to achieve optimal performance.