MySQL data migration to MSSQL-take the Xiaomi database as an example-test that the 828W speed can reach 2 minutes 11 seconds. here, the ConcurrentQueue is used in. NET Framework 4.0 or a later version. Class
MSDN describes it as follows:
ConcurrentQueue Class is a set of thread-safe first-in-first-out (FIFO.
ConcurrentQueue All the public and protected members of IS thread-safe and can be used from multiple threads at the same time.
Two threads are used, one reading and one writing.
ConcurrentQueue Implementation method:
(FIFO) set:
ConcurrentQueue
DataTableList = new ConcurrentQueue
();
Read:
1 private void MainThread () 2 {3 if (DataTableList. count <_ pageCount) // you can obtain 4 {5 for (int index = 0; index <_ pageCount; index ++) again after the maximum value of the queue) 6 {7 DataTable dt = null; 8 try 9 {10 int counts = index = 0? Index: (index * PageSize-1); 11 dt = MySql. getDataTable (counts, PageSize); 12 DataTableList. enqueue (dt); 13 Console. writeLine ("Read {0}/t {1}/{2}", DateTime. now. toString ("HH: mm: ss. fff "), index, counts); 14} 15 catch (Exception) 16 {} 17 ThreadPool. queueUserWorkItem (Thread1, dt); 18} 19} 20}
Write:
1 private void Thread1 (object state) 2 {3 var dt = (DataTable) state; 4 MsSql. insert (dt); 5 Console. writeLine ("Write {0}", DateTime. now. toString ("HH: mm: ss. fff "); 6 OK _WORK_COUNT ++; // number of processed tasks + 1 7 // Remove 8 if (DataTableList. count> 0) 9 {10 DataTableList. tryDequeue (out dt); 11} 12}
The method for reading MySQL databases is simple:
The MySqlDataAdapter. Fill (DataTable dataTable) method fills in data.
The method for writing data to the MSSQL database is also simple:
The SqlBulkCopy. WriteToServer (DataTable dataTable) method inserts data in batches.
After many tests, the optimization on the program is basically in place, but I know there are certainly some areas for improvement. please kindly advise.
I/O is the main performance bottleneck. let's take my own example:
The local machine uses HHD to store MySQL and MSSQL databases, regardless of one of November 3, and the data migration program.
Read and write 5000 data records at a time. the single-thread test result is: 41 minutes and 31 seconds.
Create a ram disk on the local machine to store MySQL and MSSQL databases. The data migration program is also stored here.
20 million pieces of data are read and written at a time. the result of a single-thread test is 3 minutes 11 seconds.
Change to ConcurrentQueue After the multi-thread synchronization thread is secure, 20 million pieces of data are read and written at a time. Results of the multi-thread test: 2 minutes 11 seconds
If you do not need to read or write data in order, you can discard the ConcurrentQueue. To achieve higher efficiency and faster read/write speed.
If SqlBulkCopyOptions is not set to UseInternalTransaction (transaction), it can be a little faster.
If the target database is an Oracle database, the average write speed of Oracle is 0.1-faster than that of MSSQL ~ 0.35 seconds.
Of course, in any case, the actual test data is different from the test data I published. after all, the use environment is different.
This test result is not professional. please forgive me.
Sample source code: Https://gitcandy.com/Repository/Tree/MySQLToMSSQL-MultiThread-Queue-Safey
Note:
FIFO: the "first-in-first-out method" is a scheduling algorithm. It describes the first-come-first-served method used by a queue: The first entry into the queue will be completed first, and the first entry must be waited.
See the English Wikipedia: http://en.wikipedia.org/wiki/FIFO_ (computing)
The Chinese version of Wikipedia is too concise: http://zh.wikipedia.org/zh-cn/fifo
HHD: Hard Disk Drive. for details, refer to Hard Disk-Baidu Encyclopedia.
RAM Disk: for details, refer to: RAM drive-Baidu Encyclopedia