May 1, 2006 at 1:10 pm
Hello,
Although a similar thread exists, the replies are useless.
I am running SQL Server 2000 (local) and have a Pervasive database as a linked server.
I need to update a Pervasive table 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')
Creating objects on the linked server is out of the question, so there is no need ask why.
How can I get this to run faster?
May 1, 2006 at 5:50 pm
Check the execution plan.
SQL Server cannot use indexes on linked server, that's why it downloads the whole table to local server, applies updates and uploads modified table back to the linked server in order to apply updates over there.
I would create staging table on linked server to insert rows local table to be involved in UPDATE and create trigger INSTEAD OF INSERT performig UPDATE on target table using the data inserted into staging table.
_____________
Code for TallyGenerator
May 4, 2006 at 10:40 am
Unfortunately, doing anything on the linked server is out of the question..as a matter of policy.
I've tried the SQL Server OPENQUERY function, but it only works with SELECT statements, not INSERT or UPDATE statements.
May 5, 2006 at 2:57 am
Try simple UPDATE, without joins:
UPDATE LinkedServerName.DBName.dbo.TblName
SET [LinkCol_1] = LocalSQLTbl.[LocalCol_1]
FROM LocalSQLTbl
WHERE LocalSQLTbl.[LocalCol_2]='XYZ'
AND LocalSQLTbl.[LocalCol_ID] = LinkedServerName.DBName.dbo.TblName.[LinkCol_ID]
AND LinkedServerName.DBName.dbo.TblName.[LinkCol_3]='ABC'
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply