Distributed SELECT and DELETE performance

My first reaction would be to see what is being returned in the SELECT query. It could be 400 columns of blob data!

Dont forget a SELECT query returns data as well as messages (such as 1 row(s) affected), whereas the DELETE will just return messages. The time taken for a query to complete includes the return!

As Matt and Kev says, the select returns every column in the select statement due to select * and i recommend you to change it to Select c.*

Your where clause filters the data anyway to only display data from C that does not have any match in GC, but SQL have to get all the records from GC anyway to do the filter.

In the delete statement the linked server will only return gc.LOPRECID to delete all records from c that doesnt have a match in GC.

IF you want even more performance i recommend you to do a "OPENQUERY".

SELECT * FROM dbo.Calls c
LEFT JOIN (SELECT LOPRECID COLLATE DATABASE_default FROM OPENQUERY ('yourlinked_server_name', SELECT LOPRECID FROM GM.dbo.cal)) AS GC
ON c.GMID = gc.LOPRECID COLLATE DATABASE_default
WHERE gc.LOPRECID IS NULL

(Or something like that)

Sorry for the layout of the code!

It's really difficult to answer that one based on the information present, but it's pretty likely that in the top query it is returning a lot more data than it needs to. I have also seen issues whereby LOB or In-Row Overflow type columns can take a long time to return data based on certain network conditions. I've never nailed it down fully as to why, however.