How Can I Efficiently Insert 100,000 Rows into a MySQL Database?

Efficient Insertion of Rows into MySQL Database

Inserting large volumes of data into a MySQL database can pose a challenge in terms of efficiency. This question explores the performance of two insertion methods and offers a solution that significantly improves insertion time.

The initial code utilizes a single INSERT statement for each row, leading to a time of 40 seconds for 100k insertions. To improve this, the question considers inserting multiple rows simultaneously via a DataTable/DataAdapter or a single INSERT statement with multiple values.

The solution presented in the answer uses a single INSERT statement with 100k rows listed as values. This method dramatically reduces insertion time to a mere 3 seconds. The SQL statement generated is as follows:

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

To prevent code injection, the MySqlHelper.EscapeString method is employed to sanitize the values before insertion. By avoiding individual INSERT statements and utilizing a single statement with multiple values, the insertion process is significantly expedited.

The above is the detailed content of How Can I Efficiently Insert 100,000 Rows into a MySQL Database?. For more information, please follow other related articles on the PHP Chinese website!