How Can I Optimize MySQL Row Insertion for Faster Data Management?

Optimized Row Insertion into MySQL Database

The performance of row insertion into a MySQL database is crucial for efficient data management. Conventional methods using individual insert queries can be time-consuming. Here's a highly optimized approach that significantly speeds up the insertion process.

Instead of executing numerous individual INSERT statements, this improved method utilizes a single multi-row INSERT statement to insert multiple rows simultaneously. By avoiding repeated overhead and creating a large batch of data to insert, the database can process the operation far more efficiently.

The code below demonstrates this technique:

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

By leveraging this method, the insertion of 100k rows can be completed within 3 seconds, a dramatic improvement over the original 40 seconds. The generated SQL statement, as shown below, demonstrates the insertion of multiple rows in a single statement:

INSERT INTO User (FirstName, LastName) VALUES ('test','test'),('test','test'),... ;

To ensure data integrity and prevent code injection attacks, the MySqlHelper.EscapeString method is employed to escape special characters within the inserted data.

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