SQL Server Performance Forum – Threads Archive

distributed query running long

Hi All, I have a question regarding running a query locally vs running it as a distributed query from a remote server. Basically, I have a query ‘select max(id) from tableA’ that when run on the local server, runs in less than 5 seconds. When i run this from a remote server, using linked server, it can run up to 5 minutes? I tried to view the execution plan of the query remotely to see if there would be a table scan used but it could not tell me that. There is a clustered index on ‘id’ column. Can someone explain why it takes much longer remotely? Or how i can confirm this statistically? Thanks in advance.

For queries on Linked Servers its better to use with OPENQUERY for better execution. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hmm, from what ive read on this site under the Linked Servers place is: If you need to access remote data from within a query, it is more efficient to perform a linked server query (after having created a linked server) rather than using an ad hoc query that uses the OPENROWSET or the OPENDATASOURCE functions. [7.0, 2000] Updated 2-16-2004 Forgive my lack of knowledge but is OPENROWSET/OPENDATASOURCE what you mean when you say OPENQUERY or are they different? I also see here that: The OPENQUERY function is used to specify that a distributed query be processed on the remote server instead of the local server. [7.0, 2000] Updated 2-16-2004
–> which is what should be achieved i think I checked the execution plan of my statement and it says it is a remote query which i interpret to mean the processing is run on the remote server.

]]>