Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

remote table update performance Expand / Collapse
Author
Message
Posted Thursday, October 11, 2012 5:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:13 PM
Points: 100, Visits: 338
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
Post #1371837
Posted Monday, October 15, 2012 4:52 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 2,924, Visits: 2,943
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,
SQL Server developer at Seavus
www.seavus.com
Post #1372990
Posted Tuesday, October 16, 2012 2:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:13 PM
Points: 100, Visits: 338
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
Post #1373520
Posted Tuesday, October 16, 2012 2:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:53 PM
Points: 12,903, Visits: 31,971
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1373527
Posted Tuesday, October 16, 2012 4:57 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 2,924, Visits: 2,943
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,
SQL Server developer at Seavus
www.seavus.com
Post #1373581
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse