September 24, 2006 at 6:10 pm
Hello,
I am running SQL Server 2000 (local) and have another database on a linked server.
I need to update the remote table (table in linked server) using a join on a local table.
The following statement works, but takes FOREVER:
UPDATE LnkSvrTbl SET [LinkCol_1] = LocalSQLTbl.[LocalCol_1]
FROM LocalSQLTbl INNER JOIN LinkedServerName.DBName..TblName LnkSvrTbl ON
LocalSQLTbl.[LocalCol_ID] = LnkSvrTbl .[LinkCol_ID]
WHERE (LocalSQLTbl.[LocalCol_2]='XYZ')AND(LnkSvrTbl.[LinkCol_3]='ABC')
How can I get this to run faster?
Should I never join a local table and a remote table?. That makes linked servers useless?.
Any suggestions will be greatly appreciated.
Thanks,
Ganesh
September 25, 2006 at 7:11 am
You could try to reduce the rows selected in the Linked Server like this
UPDATE u
SET [LinkCol_1] = l.[LocalCol_1]
FROM OPENQUERY(LinkedServerName, 'SELECT [LinkCol_ID],[LinkCol_1] FROM DBName..TblName WHERE LinkCol_3]=''ABC''') u
INNER JOIN LocalSQLTbl l
ON l.[LocalCol_ID] = u.[LinkCol_ID]
WHERE l.[LocalCol_2] = 'XYZ'
but if the number of rows to be updated in the Linked Server is large then I suggest using DTS to transfer the [LocalCol_2] = 'XYZ' rows to the linked server and doing the JOIN/UPDATE there.
Far away is close at hand in the images of elsewhere.
Anon.
September 26, 2006 at 4:54 pm
Joining a local table to a remote table isn't always a bad idea but
DML queries on remote servers are always painful. It is much faster to run updates on Local tables joined to remote tables.
Assuming you need to run this query on your local server, here is a trick to make it much faster.
Let's assume you have table X on server A (local) and Table Y on server B (remote)
EXEC B.master.dbo.sp_executeSQL N'UPDATE db.Y
SET Y.col = X.Col
FROM db.Y
INNER JOIN A.db.dbo.X X
ON ...'
Naturally you will need to create a linked server pointing to A on B. Let me know if you have any questions.
Cheers,
Robert Cary
tsqlland.blogspot.com
SQL guy and Houston Magician
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply