What is the most efficient way to insert a large number of rows from MS SQL to MySQL?

Question

I need to transfer a large number of rows from a SQL Server database to a MySQL db (MyISAM) on a daily basis. I have the MySQL instance set-up as a linked server.

I have a simple query which returns the rows that need to be transferred. The number of rows will grow to approximately 40,000 rows, each row has only 2 char(11) columns. This should equate to roughly 0.8MB in size. The execution time of the SELECT query is negligible. I'll be truncating the MySQL table prior to inserting the records each day.

I'm using an INSERT...SELECT query into the MySQL linked server to perform the insert. With 40,000 rows it takes approximately 40 seconds. To see how long it would take to move that number of rows from one MySQL table to another I executed a remote query from MSSQL and it took closer to 1 second.

I can't see much of what's going on looking at the execution plan in SSMS but it appears as though an INSERT statement is being executed for every one of the rows rather than a single statement to insert all of the rows.

What can I do to improve the performance? Is there some way I can force the rows to be inserted into MySQL in a single statement if that is what's going on?

Answer 1

The problem is that the table you are selecting from is on the local server and the table you are inserting to is on the remote server. As such the linked server is going to have to translate each row into a INSERT INTO Table (Field1, Field2) VALUES ('VALUE1','VALUE2') or similar on the MySQL server. What you could do is to keep a checksum on each row in the SQL server. Instead of truncating and reinserting the entire table you can simply delete and reinsert changed and new records. Unless most of your records change every day this should cut the amount of data you have to transfer down enourmously without having to mess about exporting and reimporting text files.

Answer 2

dump into a file and then user LOAD DATE INFILE

data inserts from a file are much quicker

Answer 3

I am not sure whether that makes it faster but a bulk download and upload would be the alternative.

On the mySQL side you could do a LOAD DATA INFILE

Don't know how to unload it on SQL Server side but there is probably something similar.

Answer 4

LOAD DATA INFILE is much faster in MySQL than INSERT. If you can set up your MS SQL server to output a temporary CSV output file, you can then pull it in to MySQL either with the commandline mysqlimport tool, or with LOAD DATA INFILE in a MySQL SQL statement.