MySQL Update from MSSQL - Very Slow !

  • 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

  • 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!

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

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

  • 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!

  • 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.

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

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