SQL Server 2008R2 to MySQL via OPENQUERY Performance Issues

Posted on 2011-05-11
Last Modified: 2012-05-11
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

Question by:Candidochris
    LVL 51

    Accepted Solution

    export to flat file and import into mysql
    LVL 51

    Assisted Solution

    you should reach to speed of 10K per second by exporting & importing...
    LVL 51

    Assisted Solution

    if SQL is not compatible with insert/update then you just insert into a temp table then merge records on MySQL

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now