Linked Server performance?

Question

Linked Server performance?

Michael Lane-394756

Old Hand

Points: 311
More actions
November 13, 2007 at 2:34 pm

#70997

Hi,
I hope this is the appropriate location for this question. I am using SQL Server 2000 on two different servers and have created linked server connections on both of them so they can communicate with each other.
When I create a query that accesses a remote table on the linked server, how is the where statement evaluated?
I have read contradictory information on the msdn site (although, it's probably just my interpretation that is contradictory). I read that the query will be passed to the linked server and it will be evaluated by the data provider up to its capabilities. On the other hand, I've also read that SQL server will scan the entire remote table and perform the query evaluations locally...
So, when I have a query that accesses data on a linked server where does the 'where' clause get evaluated? Does the provider pass the entire table back to the local server, or only the rows that satisfy the where clause? Where would sorting and grouping take place, on the local server or the provider?
Thanks,
Michael

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

From my understanding, if the query is completely on the remote server, it is passed through (WHERE clause and all). If this is a distributed query, meaning you are joining local and remote tables, the entire remote table gets pulled and the local server runs the query.

However I'll do some checking to be sure.

Michael Lane-394756 Old Hand Points: 311 More actions · Answer 2

I see. That makes sense.

So, for large tables in a distributed query, I will want to design it such that a subset of the remote data is returned and joined on, if possible.

Thanks for the help.

agnesloyola Old Hand Points: 352 More actions · Answer 3

I have written an stored procedure that read some records from the remote table on the linked server.Actually whats the problem is When I execute the Stored Procedure ,the execution takes more than 30 minutes.Eventhough its taking such a long time its not returning any records.Just an message is there "Executing Query".Can anyone help me please?For more than a week am working on this ,why its not returning any record.