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