Extremely slow insert OpenQuery performance on SQL Server to MySQL linked server

U开发者_运维百科sing SQL Server Management Studio to copy the entire contents of a table from SQL Server to an identical table on a MySQL machine. The MySQL db is connected to Management Studio as a linked server using MySQL ODBC 5.1 driver. Using a simple statement works fine, but executes extremely slowly.

INSERT INTO openquery(MYSQL, 'select * from Table1')
SELECT * from MSSQL..Table2

I have a table with about 450,000 records and it takes just over 5 hours to transfer. Is this normal? I don't have prior experience linking MySQL servers.

How long does it take to just run the "SELECT * from MSSQL..Table2", if you run it from management studio?

There are multiple reasons why your query may be slow:

• Whenever you do a massive bulk copy, you usually don't do it all in one shot, because large insert/update/delete transactions are very expensive, as the DB has to be prepared to roll-back at any time until the transaction completes. It is better to copy in batches (say 1000 records at a time). A good tool for doing this bulk copy is SSIS (which comes with SQL Server), which can do this batching for you.

• You should explicitly specify the sort order on the table you are copying from, especially if you are copying into a table which will have a clustered index. You should make sure you are inserting in the sort order of the clustered index (i.e. the clustered index is usually an int/bigint so ensure records are inserted in order 1,2,3,4 not 100,5,27,3 etc.)