Execute MySQL batch insert in Java

1000 insert method comparison.

Method 1:

Java code

    conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASS);        pstmt = conn                .prepareStatement("insert into loadtest (id, data) values (?, ?)");        for (int i = 1; i <= COUNT; i++) {            pstmt.clearParameters();            pstmt.setInt(1, i);            pstmt.setString(2, DATA);            pstmt.execute();        }

 

MyISAM: 246.6 seconds, InnoDB: 360.2 seconds

Method 2: Use transactions without automatic commit

Java code

conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASS);        conn.setAutoCommit(false);        pstmt = conn                .prepareStatement("insert into loadtest (id, data) values (?, ?)");        for (int i = 1; i <= COUNT; i++) {            pstmt.clearParameters();            pstmt.setInt(1, i);            pstmt.setString(2, DATA);            pstmt.execute();            if (i % COMMIT_SIZE == 0) {                conn.commit();            }        }        conn.commit();

 

InnoDB: 31.5 seconds

Method 3: executebatch

Java code

conn = DriverManager.getConnection(JDBC_URL                + "?rewriteBatchedStatements=true", JDBC_USER, JDBC_PASS);        conn.setAutoCommit(false);        pstmt = conn                .prepareStatement("insert into loadtest (id, data) values (?, ?)");        for (int i = 1; i <= COUNT; i += BATCH_SIZE) {            pstmt.clearBatch();            for (int j = 0; j < BATCH_SIZE; j++) {                pstmt.setInt(1, i + j);                pstmt.setString(2, DATA);                pstmt.addBatch();            }            pstmt.executeBatch();            if ((i + BATCH_SIZE - 1) % COMMIT_SIZE == 0) {                conn.commit();            }        }        conn.commit();

 

InnoDB: 5.2 seconds

Required
1) rewritebatchedstatements = true
2) useserverprep;ts = true

Method 4: load and then commit

Java code

conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASS);        conn.setAutoCommit(false);        pstmt = conn.prepareStatement("load data local infile '' "                + "into table loadtest fields terminated by ','");        StringBuilder sb = new StringBuilder();        for (int i = 1; i <= COUNT; i++) {            sb.append(i + "," + DATA + "\n");            if (i % COMMIT_SIZE == 0) {                InputStream is = new ByteArrayInputStream(sb.toString()                        .getBytes());                ((com.mysql.jdbc.Statement) pstmt)                        .setLocalInfileInputStream(is);                pstmt.execute();                conn.commit();                sb.setLength(0);            }        }        InputStream is = new ByteArrayInputStream(sb.toString().getBytes());        ((com.mysql.jdbc.Statement) pstmt).setLocalInfileInputStream(is);        pstmt.execute();        conn.commit();

This is 4.6 seconds at most.