MySQL data migration to MSSQL-take the Xiaomi database as an example-test the 828W speed can reach 2 points faster-mysql-mysql tutorial

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.