Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two database servers, connected via Linked Servers. Both are SQL Server 2008R2 databases, and the linked server connection is made via a regular "SQL Server" link, using the current login's security context. The linked servers are both in the same datacentre, so the connection shouldn't be an issue.

I use the following query to check which values of the column identifier are available remotely, but not locally.

SELECT 
    identifier 
FROM LinkedServer.RemoteDb.schema.[TableName]

EXCEPT

SELECT DISTINCT
    identifier 
FROM LocalDb.schema.[TableName] 

On both tables are non-clustered indexes on the column identifier. Locally are around 2.6M rows, remotely only 54. Yet, when looking at the query plan, 70% of the execution time is devoted to "executing remote query". Also, when studying the complete query plan, the number of estimated local rows is 1 instead of 2695380 (which is the number of estimated rows when selecting only the query coming after EXCEPT). Execution plan When executing this query, it takes a long time indeed.

It makes me wonder: Why is this? Is the estimation "just" way off, or are remote queries on linked servers really that expensive?

share|improve this question

migrated from stackoverflow.com Jan 18 '12 at 12:54

This question came from our site for professional and enthusiast programmers.

1  
BTW: It is "estimated number of executions" you should be looking at for the index seek. The estimated number of rows is rows output per execution which won't be related to the number of rows in the table itself unless the plan has a full scan. –  Martin Smith Jan 18 '12 at 14:21
add comment

3 Answers 3

up vote 4 down vote accepted

The plan you have at the moment looks like the most optimal plan to me.

I don't agree with the assertion in the other answers that it is sending the 2.6M rows to the remote server.

The plan looks to me as though for each of the 54 rows returned from the remote query it is performing an index seek into your local table to determine whether it is matched or not. This is pretty much the optimal plan.

Replacing with a hash join or merge join would be counterproductive given the size of table and adding an intermediate #temp table just adds an additional step that doesn't seem to give you any advantage.

share|improve this answer
add comment

Connecting to a remote resource is expensive. Period.

One of the most expensive operations in any programming environment is network IO (though disk IO tends to dwarf it).

This extends to remote linked servers. The server calling the remote linked server needs to first establish a connection, then a query needs to be executed on the remote server, results returned and the connection closed. This all takes time over the network.


You should also structure your query in such a way that you transfer the minimum data across the wire. Don't expect the DB to optimize for you.

If I were to write this query, I would select the remote data into a table variable (or into a temp table) and then use this in conjunction with the local table. This ensures that only data that needs to be transferred will.

The query you are running can easily be sending 2.6M rows to the remote server in order to process the EXCEPT clause.

share|improve this answer
    
Ok, so it has high startup costs to set up the connection. The query needs to be sent, processed remotely (no network needed for that one), and finally the results sent back and processed. But it won't take minutes to send data over a network connection, will it? –  vstrien Jan 18 '12 at 10:31
    
@vstrien - It might. Depends on the network connection, latency, saturation and other factors. Point being - it is not deterministic. –  Oded Jan 18 '12 at 10:32
    
@vstrien - Added more information in my answer. I believe the query as written will send the local rows to the remote server for processing. –  Oded Jan 18 '12 at 10:36
1  
Where do you deduce the fact that it is sending the 2.6M rows to the remote server from? I haven't much experience with plans with remote query operators but it looks as though the 54 rows are coming out of the remote query operator then it is doing the anti semi join against the local table. –  Martin Smith Jan 18 '12 at 11:17
2  
@Lieven - Might be logical but don't think it is correct from the plan shown. –  Martin Smith Jan 18 '12 at 12:44
show 1 more comment

Oded is right, the performance problem is caused by sending the 2.6M rows to your remote server.

To fix this issue you can force the remote data (54 rows) being send to you by using a temp or an in memory table.

Using a temporary table

SELECT  identifier 
INTO    #TableName
FROM    LinkedServer.RemoteDb.schema.[TableName]

SELECT  identifier
FROM    #TableName
EXCEPT
SELECT  DISTINCT identifier 
FROM    LocalDb.schema.[TableName] 

DROP    #TableName
share|improve this answer
    
Using a temporary table might help with cardinality estimates in any event although a nested loops seems reasonable for only 54 rows. –  Martin Smith Jan 18 '12 at 11:18
    
Using a temporary table works right with 54 rows; but in cases with large tables on both sides it isn't feasible anymore. What would your solution be for two equally-sized "huge" tables? Creating a UserTable, in another database? –  vstrien Jan 18 '12 at 11:50
    
@vstrien - there isn't really a good solution for two equaly-sized huge tables. Perhaps creating a Distributed Partitioned View is of interest to you but I have no experience whatsoever with it. –  Lieven Keersmaekers Jan 18 '12 at 12:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.