How Can I Achieve Ultra-Fast Bulk Insertion of Rows into MySQL?

Optimizing MySQL Row Insertion: Achieving Ultra-Fast Bulk Insertion

In this article, we address the need for efficient row insertion into a MySQL database. We present an optimized solution that dramatically reduces insertion time, improving overall database performance.

Background

Many developers encounter performance bottlenecks while inserting large volumes of data into MySQL. While loop-based approaches using individual SQL statements are functional, they can be inefficient and slow.

Problem Statement

In the provided example, the loop-based code takes 40 seconds to insert 100,000 rows into a MySQL database, highlighting the need for a more efficient approach.

Solution

We replace the loop-based approach with a bulk insertion technique using a StringBuilder to construct a single SQL statement containing multiple rows to be inserted at once. By utilizing this approach, we achieve significant performance improvements.

Example Code

The optimized code is provided below:

public static void BulkToMySQL()
{
    string ConnectionString = "server=192.168.1xxx";
    StringBuilder sCommand = new StringBuilder("INSERT INTO User (FirstName, LastName) VALUES ");           
    using (MySqlConnection mConnection = new MySqlConnection(ConnectionString))
    {
        List<string> Rows = new List<string>();
        for (int i = 0; i < 100000; i++)
        {
            Rows.Add(string.Format("('{0}','{1}')", MySqlHelper.EscapeString("test"), MySqlHelper.EscapeString("test")));
        }
        sCommand.Append(string.Join(",", Rows));
        sCommand.Append(";");
        mConnection.Open();
        using (MySqlCommand myCmd = new MySqlCommand(sCommand.ToString(), mConnection))
        {
            myCmd.CommandType = CommandType.Text;
            myCmd.ExecuteNonQuery();
        }
    }
}

Results

Using the bulk insertion approach, the insertion time for 100,000 rows drops from 40 seconds to a mere 3 seconds, demonstrating a remarkable 93% performance improvement.

Additional Considerations

1. Security: We have added MySqlHelper.EscapeString to prevent code injection.
2. Tuning: You can further fine-tune the performance by adjusting the batch size and optimizing database indexes.

Conclusion

By implementing this bulk insertion technique, developers can significantly enhance the efficiency of their MySQL database insertion operations. This method reduces insertion time, improves overall performance, and ensures data integrity with the added security measure.

The above is the detailed content of How Can I Achieve Ultra-Fast Bulk Insertion of Rows into MySQL?. For more information, please follow other related articles on the PHP Chinese website!