remote table update performance

Question

Post reply

remote table update performance

RightOnTarget

Hall of Fame

Points: 3041
More actions
October 11, 2012 at 5:53 pm

#283252

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

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

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

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

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

RightOnTarget Hall of Fame Points: 3041 More actions · Answer 2

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

Lowell SSC Guru Points: 323518 More actions · Answer 3

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!

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 4

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