SQL Server 2008R2 to MySQL via OPENQUERY Performance Issues

I'm running into some performance issues which may or may not be resolveable but I wanted to ask the experts.

My local SQL Server is running SQL Server 2008R2 and Windows Server 2008 64-bit
w/ 96gb ram with twin Xeon processors.
It's on a gigabit network with 10/100MBPS  WAN

The remote is a Linux box on a similar connection, 32-bit, 8gb ram.

 am moving data on a weekly basis from a large (3,000,000 rows) SQL server database table to a MYSQL table on a remote server.

The data is sent to my local SQL Server via replication, which takes about 10 minutes to complete.

I then have a stored procedure which transfers the data to the linked MySQL server via OPENQUERY. Four part naming does not work with the MySQL ODBC driver, from what I understand.

My transfer rate is 40ms/record -- transferring 10k records in 4 minutes. The problem is that the complete transfer on this table would take 26 hours. I have to use TRUNCATE and INSERT because SQL Server is not compatible with MySQL's INSERT... ON DUPLICATE KEY UPDATE

I'm trying to find a faster way to do this, if anyone has been in my shoes please let me know.

I have considered setting up MySQL on my local server and then setting up replication on the remote to see if the SQL-MySQL transfer on the server will be faster (I believe replication is transactional between mysql servers, so it should theoritically be faster). Since I am only updating, I am hoping there is some sort of setting I can tweak to get more performance.

INSERT OPENQUERY(T14,'SELECT Id, IsActive, DateAdded, DateModified, ApplicationId, FitmentId, ItemId, GroupId FROM RPM_ItmApplicationsIndex')
SELECT TOP 10000 Id, IsActive, DateAdded, DateModified, ApplicationId, FitmentId, ItemId, GroupId 
FROM ItmApplicationsIndex

Open in new window

CandidochrisAsked:
Who is Participating?
 
HainKurtSr. System AnalystCommented:
export to flat file and import into mysql
0
 
HainKurtSr. System AnalystCommented:
you should reach to speed of 10K per second by exporting & importing...
0
 
HainKurtSr. System AnalystCommented:
if SQL is not compatible with insert/update then you just insert into a temp table then merge records on MySQL
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.