distributed query running long | SQL Server Performance Forums

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
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.