Cut MyBatis Batch Insert Time from 80 seconds to 1 second for 100k Records

Introduction

When inserting more than 100,000 rows with several numeric columns, a simple loop insert takes around 80 seconds. The article demonstrates how to achieve sub‑second performance using JDBC batch processing, manual transaction handling, and MyBatis/MyBatis‑Plus batch APIs.

JDBC ordinary insert

/**
 * JDBC - ordinary insert (loop)
 */
public class JDBCDemo {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "123456";
        String driver = "com.mysql.jdbc.Driver";
        String sql = "INSERT INTO fee(`owner`,`fee1`,`fee2`,`fee3`,`fee4`,`fee5`) VALUES (?,?,?,?,?,?)";
        Connection conn = null;
        PreparedStatement ps = null;
        long start = System.currentTimeMillis();
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= 100000; i++) {
                ps.setString(1, "o" + i);
                ps.setBigDecimal(2, new BigDecimal("11111.111"));
                ps.setBigDecimal(3, new BigDecimal("11111.111"));
                ps.setBigDecimal(4, new BigDecimal("11111.111"));
                ps.setBigDecimal(5, new BigDecimal("11111.111"));
                ps.setBigDecimal(6, new BigDecimal("11111.111"));
                ps.executeUpdate();
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); }
            if (ps != null) try { ps.close(); } catch (SQLException e) { e.printStackTrace(); }
        }
        long end = System.currentTimeMillis();
        System.out.println("100k rows ordinary insert: " + (end - start) + " ms");
    }
}

The execution result (≈80 s) is shown in the first screenshot.

JDBC batch insert

/**
 * JDBC - batch insert
 */
public class JDBCPlusDemo {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true";
        String user = "root";
        String password = "123456";
        String driver = "com.mysql.jdbc.Driver";
        String sql = "INSERT INTO fee(`owner`,`fee1`,`fee2`,`fee3`,`fee4`,`fee5`) VALUES (?,?,?,?,?,?)";
        Connection conn = null;
        PreparedStatement ps = null;
        long start = System.currentTimeMillis();
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
            ps = conn.prepareStatement(sql);
            conn.setAutoCommit(false);
            for (int i = 1; i <= 100000; i++) {
                ps.setString(1, "o" + i);
                ps.setBigDecimal(2, new BigDecimal("11111.111"));
                ps.setBigDecimal(3, new BigDecimal("11111.111"));
                ps.setBigDecimal(4, new BigDecimal("11111.111"));
                ps.setBigDecimal(5, new BigDecimal("11111.111"));
                ps.setBigDecimal(6, new BigDecimal("11111.111"));
                ps.addBatch();
                if (i % 1000 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            ps.executeBatch();
            ps.clearBatch();
            conn.commit();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); }
            if (ps != null) try { ps.close(); } catch (SQLException e) { e.printStackTrace(); }
        }
        long end = System.currentTimeMillis();
        System.out.println("100k rows batch insert: " + (end - start) + " ms");
    }
}

The batch version finishes in about 1 second. Important details highlighted:

Use PreparedStatement methods addBatch(), executeBatch(), clearBatch().

Set the JDBC URL parameter rewriteBatchedStatements=true to let MySQL rewrite the batch into a single multi‑row statement.

Do not terminate the SQL statement with a semicolon; otherwise a BatchUpdateException occurs.

Disable auto‑commit and commit manually after the batch to reduce disk I/O and lock contention.

Split the batch into chunks (e.g., 1000 rows) to avoid excessive memory consumption.

MyBatis / MyBatis‑Plus setup

Maven dependencies include mysql‑connector‑java, mybatis‑plus‑boot‑starter, Lombok, and Spring Boot test starter. The application.properties file also contains the rewritten URL:

# datasource configuration
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
spring.datasource.username=root
spring.datasource.password=123456

Entity and mapper definitions (simplified):

@TableName("fee")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Fee {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String owner;
    private BigDecimal fee1;
    private BigDecimal fee2;
    private BigDecimal fee3;
    private BigDecimal fee4;
    private BigDecimal fee5;
}

@Mapper
public interface FeeMapper extends BaseMapper<Fee> {
    int insertByOne(Fee fee);
    int insertByForeach(@Param("feeList") List<Fee> feeList);
}

MyBatis ordinary insert

@Test
public void mpDemo1() {
    List<Fee> feeList = getFeeList();
    long start = System.currentTimeMillis();
    feeService.saveByFor(feeList);
    long end = System.currentTimeMillis();
    System.out.println("100k rows ordinary MyBatis insert: " + (end - start) + " ms");
}

The result is again around 80 seconds, matching the JDBC loop.

Foreach dynamic SQL insert

@Test
public void mpDemo2() {
    List<Fee> feeList = getFeeList();
    long start = System.currentTimeMillis();
    feeService.saveByForeach(feeList);
    long end = System.currentTimeMillis();
    System.out.println("100k rows foreach insert: " + (end - start) + " ms");
}

Initially this fails because the generated SQL exceeds MySQL’s default max_allowed_packet (4 MB). After increasing the limit to 10 MB with SET GLOBAL max_allowed_packet=10*1024*1024;, the execution time drops to about 3 seconds. The approach is still slower than true batch processing and depends on server‑side configuration.

Manual batch insert with MyBatis

@Transactional
@Override
public int saveByBatch(List<Fee> feeList) {
    int res = 0;
    SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
    FeeMapper feeMapper = sqlSession.getMapper(FeeMapper.class);
    for (int i = 1; i <= feeList.size(); i++) {
        res += feeMapper.insertByOne(feeList.get(i - 1));
        if (i % 1000 == 0) {
            sqlSession.commit();
            sqlSession.clearCache();
        }
    }
    sqlSession.commit();
    sqlSession.clearCache();
    return res;
}

Performance is comparable to the pure JDBC batch version (≈1 second). The same prerequisites apply: enable rewriteBatchedStatements=true and use manual transaction control.

MyBatis‑Plus saveBatch()

@Test
public void mpDemo4() {
    List<Fee> feeList = getFeeList();
    long start = System.currentTimeMillis();
    feeService.saveBatch(feeList);
    long end = System.currentTimeMillis();
    System.out.println("100k rows MP saveBatch: " + (end - start) + " ms");
}

This built‑in method finishes in about 2 seconds. It also requires the URL flag rewriteBatchedStatements=true; otherwise the speed drops dramatically, as shown in the accompanying screenshot.

Key takeaways

Enable rewriteBatchedStatements=true in the MySQL JDBC URL; it is essential for any batch‑insert technique.

Turn off auto‑commit and commit manually after a reasonable batch size to minimise disk writes and lock contention.

Split large batches (e.g., 1 000 rows) to keep memory usage under control.

Foreach dynamic SQL can work after raising max_allowed_packet, but it is less efficient and less portable.

For pure JDBC, a custom batch implementation is the fastest.

For MyBatis/MyBatis‑Plus, either the custom batch shown above or the MP saveBatch() method provides good performance.

Remember to always add rewriteBatchedStatements=true to the connection URL when performing batch inserts.