Posted by Erland Sommarskog on 04/25/07 21:49

(rshivaraman@gmail.com) writes:
> I am updating a local table based on inner join between local table
> and remote table.
>
> Update LocalTable
> SET Field1 = B.Field1
> FROM LinkedServer.dbname.dbo.RemoteTable B
> INNER JOIN LocalTable A
> ON B.Field2 = A.Field2
> AND B.Field3 = A.Field3
>
> This query takes 18 minutes to run.
> I am hoping to speed up the process by writing in OPENQUERY syntax.

UPDATE LocalTable
SET Field1 = B.Field1
FROM OPENQUERY(LINKEDSERVER,
'SELECT Field1, Field2, Field3 FROM dbname.dbo.RemoteTable) B
INNER JOIN LocalTable A
ON B.Field2 = A.Field2
AND B.Field3 = A.Field3

I would not really expect this to perform better.

Distributed queries are always difficult, but it's difficult to suggest
anything without further knowledge about the table. How big are the
two tables?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]