Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Server 2008R2 to MySQL via OPENQUERY Performance Issues

Posted on 2011-05-11
3
Medium Priority
?
1,195 Views
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

0
Comment
Question by:Candidochris
  • 3
3 Comments
 
LVL 61

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 35738518
export to flat file and import into mysql
0
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 2000 total points
ID: 35738523
you should reach to speed of 10K per second by exporting & importing...
0
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 2000 total points
ID: 35738548
if SQL is not compatible with insert/update then you just insert into a temp table then merge records on MySQL
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

571 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