MySQL Update from MSSQL - Very Slow !

Question

Post reply

MySQL Update from MSSQL - Very Slow !

homebrew01

SSC Guru

Points: 55548
More actions
December 2, 2014 at 10:41 am

#312826

We have a web site in development that updates a MySQL table from a SQL stored procedure using a Linked Server. The update itself is very simple, but takes almost a minute to run. If I run a hardcoded version on MySQL directly, it takes 1 second
Both servers are physically in the same rack at our hosting company.
Individually, performance on all the servers is good. Just this Linked Server to MySQL is VERY sluggish.
ODBC settings ? Connection Pooling ??
Any thoughts about what could cause such a long wait time ?
SP just uses a couple of variables to do a simple update:
update MYSQL_STAGE...wp_users
set user_login = case when @Email1 > '' then @Email1 else user_login end,
user_pass = case when @password > '' then @password else user_pass end
where id = @Member_ID

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

ahh, the pain of linked servers.

remember, when you deal with linked servers, in this situation, here's what happens:

the entire ate MYSQL_STAGE...wp_users table is copied into tempdb.

the update is calculated on the temp table.

a whole bunch of hidden update commands get created for updating the MySQL table via the ODBC driver...and those updates may be RBAR commands, one for each row., for any of the affected tables; i'm not exactly sure what the drive rdoes behind the scenes, but that is my impression.

Instead, you want to use EXECUTE AT to avoid that situation, so the work is performed directly in the linked server engine:

untested, but this is pretty darn close: this assumes the case statement is MySQL syntax compatible, which i'm not sure of ...does MySQL use a SWITCH command?

DECLARE @Member_ID int = 42

DECLARE @cmd varchar(max) = ' UPDATE wp_users set user_login = case when @Email1 > '' then @Email1 else user_login end,

user_pass = case when @password > '' then @password else user_pass end

where id = ' + convert(varchar,@Member_ID)

EXECUTE (@cmd) AT MYSQL_STAGE

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

Great ! I think that's what I need. I will tweak it & test it .

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

Worked perfectly. Run time from a minute down to 1 second. Thanks for explanation & solution

Lowell SSC Guru Points: 323518 More actions · Answer 4

homebrew01 (12/3/2014)
Worked perfectly. Run time from a minute to 1 second. Thanks for explanation & solution

excellent!

glad that worked for you!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

Is there a way to use this method, while also getting data from a MSSQL table, and inserting or updating MySQL ?? I'm guessing not.

I have another situation that does:

insert into MySQL_Stage...wp_users

select fld1, fld2,fld3

from MSSQL_Database..MyTable

I could do the SELECT into variables, then run similar code to what you suggested above.

Edit: That's what I did, which works.