How Can I Optimize MySQL Database Row Insertion for Faster Performance?

Optimizing MySQL Database Row Insertion for Efficient Performance

The challenge of inserting large volumes of rows efficiently into MySQL databases is well-known. While the provided code snippet using individual inserts may be familiar, it presents performance bottlenecks. However, there exists an alternative approach that can dramatically improve insertion speed.

Multiple Row Insertion

Instead of performing a separate insert for each row, it is more efficient to group multiple rows into a single insert statement. This technique is implemented in the following revised code:

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();
        }
    }
}

Improved Performance

This approach results in a significant reduction in insertion time. The original code required 40 seconds to insert 100k rows, while the revised code completes the task in just 3 seconds, an improvement of over 90%!

Explanation

The key to this increased efficiency lies in minimizing database round-trips. By grouping multiple rows into a single statement, we reduce the overhead associated with establishing and closing multiple database connections. The database can then process the entire group of insertions as a single operation, eliminating the need for separate execution for each row.

Security Considerations

It is important to always protect against code injection when building SQL statements. The use of MySqlHelper.EscapeString in the provided code ensures that potentially malicious input is safely escaped, preventing SQL injection vulnerabilities.

The above is the detailed content of How Can I Optimize MySQL Database Row Insertion for Faster Performance?. For more information, please follow other related articles on the PHP Chinese website!