I received a problem from one of our customer that a delete statement they run was taking very long time. The query looked like this:
DELETE FROM linkedserver.database.dbo.table
WHERE ID IN (SELECT ID FROM linkedserver.database.dbo.anothertable)
Just to generate the execution plan took nearly one hour. This was realy confused. The select statement in the IN clause took just a second. No to the interesting part of this, when I was looking in the remote server I saw that the server where I run the query did a SELECT * FROM on the table. It is not surprising that the query took long time when the table was on nearly 30GB with 21 million of rows. But why did the query returned all columns? Anyway, after some googling I find out to use OPENQUERY instead. So after changing the query to the following it worked well. Actually I was shrinking the execution time from 1 hour to 2-3 seconds.
DELETE OPENQUERY(linkedserver,
'SELECT * FROM database.dbo.table WHERE ID IN (SELECT ID FROM database.dbo.anothertable)')
