How Can Bulk Copying of DataTables Be Achieved in MySQL While Maintaining Performance?

Using MySQLBulkLoader to Bulk Copy a DataTable

Question

In transitioning from Microsoft SQL Server to MySQL, how can bulk copying of DataTables be achieved without sacrificing performance?

Answer

While it may seem intuitive to avoid writing to a CSV file before inserting data, performance benchmarks indicate otherwise. Using the MySqlBulkLoader class to bulk-load data from a CSV file significantly outperforms direct inserts with a MySqlDataAdapter.

Implementation:

<code class="csharp">string tempCsvFileSpec = @"C:\Users\Gord\Desktop\dump.csv";

using (StreamWriter writer = new StreamWriter(tempCsvFileSpec))
{
    Rfc4180Writer.WriteDataTable(rawData, writer, false);
}

var msbl = new MySqlBulkLoader(conn);
msbl.TableName = "testtable";
msbl.FileName = tempCsvFileSpec;
msbl.FieldTerminator = ",";
msbl.FieldQuotationCharacter = '"';
msbl.Load();

System.IO.File.Delete(tempCsvFileSpec);</code>

This code dumps the DataTable into a temporary CSV file, bulk-loads the data, and then deletes the file. Benchmarking shows that this approach takes only 5-6 seconds, including the time for file I/O operations. In contrast, direct insertions with MySqlDataAdapter take approximately 30 seconds.

The above is the detailed content of How Can Bulk Copying of DataTables Be Achieved in MySQL While Maintaining Performance?. For more information, please follow other related articles on the PHP Chinese website!