MySQL data migration to mssql-take Xiaomi database for example-Test 828W up to 2 minutes and 11 seconds

Source: Internet
Author: User
Tags mssql

This is the new Concurrentqueue<t> class that appears in the. NET Framework version 4.0 and above

MSDN is described in this way:

The Concurrentqueue<t> class is a thread-safe FIFO (FIFO) collection.

All public and protected members of the concurrentqueue<t> are thread-safe and can be used concurrently from multiple threads.

A total of two threads, one read one write.

How to implement Concurrentqueue<t>:

(FIFO) Collection:

New Concurrentqueue<datatable> ();

Read:

1 Private voidMainthread ()2 {3     if(Datatablelist.count < _pagecount)//is less than the maximum value of the queue and can be retrieved again4     {5          for(intindex =0; Index < _pagecount; index++)6         {7DataTable dt =NULL;8             Try9             {Ten                 intCounts = Index = =0? Index: (Index * PageSize-1); OneDT =mysql.getdatatable (counts, PageSize); A datatablelist.enqueue (DT); -Console.WriteLine ("Read {0}\t{1}/{2}", DateTime.Now.ToString ("HH:mm:ss.fff"), index, counts); -             } the             Catch(Exception) -             { } - ThreadPool.QueueUserWorkItem (Thread1, DT); -         } +     } -}

Write:

1 Private voidThread1 (ObjectState )2 {3     varDT =(DataTable) state;4 mssql.insert (DT);5Console.WriteLine ("Write {0}", DateTime.Now.ToString ("HH:mm:ss.fff"));6ok_work_count++;//Number of tasks processed +17     //Remove from task queue8     if(Datatablelist.count >0)9     {TenDatatablelist.trydequeue ( outDT); One     } A}

The way to read a MySQL database is simple:

The Mysqldataadapter.fill (DataTable DataTable) method populates the data.

The method of writing to the MSSQL database is also simple:

The Sqlbulkcopy.writetoserver (DataTable DataTable) method inserts data in bulk.

After many tests, the optimization of the program, basically in place, but I know there must be a place to improve, please all the way the great God to enlighten.

The main performance bottleneck or I/O, take my own example:

This machine uses HHD to store MySQL and MSSQL databases, regardless of 372, along with the data migration program is also placed in the HHD.

Read and write 5,000 data at a time, single-threaded test results are:41 minutes 31 seconds .

A RAM disk is created on this machine to hold the MySQL and MSSQL databases, as usual, and the data Migration program is also located here.

Read and write 20W data, single-threaded test results:3 minutes 11 seconds

And instead of concurrentqueue<t> multithreaded synchronization thread security, read and write 20W data, multithreaded test results:2 minutes 11 seconds

If the read-write data does not need to be in order, can completely discard the concurrentqueue<t>, so as to achieve higher efficiency, faster read and write speed.

If Sqlbulkcopyoptions is not set to Useinternaltransaction (transaction), it can be a little bit faster.

If the write target is an Oracle database, Oracle's average write speed is 0.1~0.35 seconds faster than MSSQL.

Of course, in any case, the actual test data and my published test data are different, after all, the use of the environment.

This test result is not professional, please forgive me.

Sample source : Https://gitcandy.com/Repository/Tree/MySQLToMSSQL-MultiThread-Queue-Safey

Comments:

FIFO: Advanced First Out method is a scheduling algorithm. It describes a first-come-first-served service used by a queue: the work that first enters the queue is completed first, and then the incoming must wait.

See 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: Full hard disk drive, see: HDD-Baidu Encyclopedia

Ram Disk: see: Ram drives-Baidu Encyclopedia

Related Article

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.