3

Using 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.

AstroCB
  • 12,337
  • 20
  • 57
  • 73
justaguy
  • 31
  • 1
  • 2
  • You're physically copying everyone of the 450,000 records from the mssql source to a new server and disk location. 5 hours is 40ms per record. Is it slow for recording data? – boisvert Apr 26 '11 at 22:35

1 Answers1

4

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.)

Gareth
  • 936
  • 6
  • 14