How to Efficiently Bulk Copy DataTables to MySQL?

Bulk Copying DataTables to MySQL with Optimal Performance

Migrating projects from Microsoft SQL Server to MySQL can encounter challenges in bulk data copying. This is because the common SqlBulkCopy API found in Microsoft's system is not directly available for MySQL.

Bulk Copy Alternatives

One alternative would be to use the MySqlBulkLoader class, but this involves exporting data to a CSV file and then importing it, which can result in performance bottlenecks.

Optimized Method with MySqlDataAdapter

However, a more efficient approach can be leveraged by utilizing MySqlDataAdapter's Update() method within a Transaction. This method allows batch inserts with configurable update batch sizes, significantly reducing execution time.

Code Example

Here's an optimized example using the MySqlDataAdapter approach:

<code class="csharp">using (MySqlTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.Serializable))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        cmd.Transaction = tran;
        cmd.CommandText = "SELECT * FROM testtable";
        using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
        {
            da.UpdateBatchSize = 1000;
            using (MySqlCommandBuilder cb = new MySqlCommandBuilder(da))
            {
                da.Update(rawData);
                tran.Commit();
            }
        }
    }
}</code>

Comparison with MySqlBulkLoader

When compared to using the MySqlBulkLoader, the MySqlDataAdapter approach outperforms in terms of execution speed. Tests with bulk inserts of 100,000 rows showed an execution time of 30 seconds using the MySqlDataAdapter, while the MySqlBulkLoader completed the process in 5-6 seconds.

Conclusion

For efficient bulk copying of DataTables to MySQL, consider using the MySqlDataAdapter's Update() method within a Transaction. This approach provides optimal performance without the need for intermediary CSV exports and imports, resulting in faster and more efficient data migrations.

The above is the detailed content of How to Efficiently Bulk Copy DataTables to MySQL?. For more information, please follow other related articles on the PHP Chinese website!