Optimizing MySQL Inserts with Multithreading, Prepared Statements, Batch and Transaction Techniques

It explains why inserting into the same MySQL table with multiple threads can be faster than a single thread, breaking down the time spent on connection establishment (30%), sending queries (20%), parsing (20%), actual insert operations (10% per row), index updates (10% per index), and closing connections (10%).

The article shows how to use multithreading for inserting into a single table and for inserting into multiple partitioned tables.

It introduces PreparedStatement as a way to pre‑compile SQL, providing a Java example that obtains a connection from a pool, prepares the statement, sets parameters in a loop, adds each execution to a batch, executes the batch, and finally returns the connection to the pool:

String sql = "insert into testdb.tuser (name, remark, createtime, updatetime) values (?, ?, ?, ?)";
for (int i = 0; i < m; i++) {
    Connection conn = myBroker.getConnection();
    PreparedStatement pstmt = conn.prepareStatement(sql);
    for (int k = 0; k < n; k++) {
        pstmt.setString(1, RandomToolkit.generateString(12));
        pstmt.setString(2, RandomToolkit.generateString(24));
        pstmt.setDate(3, new Date(System.currentTimeMillis()));
        pstmt.setDate(4, new Date(System.currentTimeMillis()));
        pstmt.addBatch();
    }
    pstmt.executeBatch();
    pstmt.close();
    myBroker.freeConnection(conn);
}

It compares ordinary INSERT statements with multi‑value INSERTs, showing that a single statement like INSERT INTO TBL_TEST (id) VALUES (1), (2), (3) reduces network I/O and parsing overhead.

The article also describes committing many INSERTs within a single transaction (e.g., committing every 1000 statements) and provides a Java method that executes a list of SQL strings inside a MySQL transaction, committing periodically and rolling back on errors:

public void ExecuteSqlTran(List
SQLStringList) {
    using (MySqlConnection conn = new MySqlConnection(connectionString)) {
        conn.Open();
        MySqlCommand cmd = new MySqlCommand();
        cmd.Connection = conn;
        MySqlTransaction tx = conn.BeginTransaction();
        cmd.Transaction = tx;
        try {
            for (int n = 0; n < SQLStringList.Count; n++) {
                string strsql = SQLStringList[n].ToString();
                if (strsql.Trim().Length > 1) {
                    cmd.CommandText = strsql;
                    cmd.ExecuteNonQuery();
                }
                if (n > 0 && (n % 1000 == 0 || n == SQLStringList.Count - 1)) {
                    tx.Commit();
                    tx = conn.BeginTransaction();
                }
            }
        } catch (System.Data.SqlClient.SqlException E) {
            tx.Rollback();
            throw new Exception(E.Message);
        }
    }
}

Finally, it notes that using these techniques can insert roughly 100 000 rows in about 10 seconds.