remote table update performance

  • Hi all,

    I am having problem updating remote table. It just taking too long. I am new to SQL Server and not sure where to check for bottle necks.

    Basically I need to update remote table with values from a local table.

    Here is the statement I use:

    UPDATE [rem_srv].DB.Schema.TableA

    set a.image_id = b.image_id

    ,a.image_name = b.image_name

    from [rem_srv].DB.Schema.TableA a, #local_temp b

    where a.image_id = b.image_id

    and a.image_name = b.image_name

    I even tried and rewrote my query into a cursor to update values one-by-one, but ... had to kill it after 10 min.

    I am talking about 850 rows!

    Where do I start looking? Can I rewrite my query? Can I use hint (hate it , but ...)?

    It is a bit urgent and any help is appreciated.

    Thanks,

    Eugene

  • Hi,

    Have you linked to the remote server?

    If you haven't, then you should do it first. Check this reference: http://msdn.microsoft.com/en-us/library/ms190479.aspx

    Regards

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • Hi,

    Yes, I did created linked server if this is what you're asking for.

    I was looking for something similar to Oracle's "Driving_site" hint to do the join on remote site.

    I believe I found it: "remote"

    Thanks,

    Eugene

  • well, linked servers may not behave the way you expect;

    typically here's what happens:

    every row in the remote table is copied to a table in tempdb.

    the update is performed on that temp table.

    the temp table is then sent back over the wire to really update the remote table.

    that can be a huge performance problem, especially with big tables.

    if your cursor were changed to create a literal string to UPDATE AT linkedserver, it would be faster;

    ie like this:

    declare @SQL varchar(1000)

    SET @SQL = 'UPDATE TableA SET image_name = ''NewName'' WHERE image_id = 42'

    EXECUTE (@SQL) AT [rem_srv];

    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!

  • Hi, check you use a user that has rights on the table you're trying to update.

    declare @SQL varchar(1000)

    SET @SQL = 'UPDATE TableA SET image_name = ''NewName'' WHERE image_id = 42'

    EXECUTE (@SQL) AS USER = 'Oracle user' --user who has rights on TableA

    AT [rem_srv]

    If still do not pass try with sp_executesql

    declare @SQL Nvarchar(1000)

    SET @SQL = 'UPDATE TableA SET image_name = ''NewName'' WHERE image_id = 42'

    EXECUTE sp_executesql @SQL

    Regards

    IgorMi

    Igor Micev,My blog: www.igormicev.com

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

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