I have a query like this:
select key, name from localtab where key not in (select key from remotetab);
The query takes forever, and I don't understand why.
localtab is local table, and remotetab is a remote table in another server. key is an int column which has a unique index in both tables. When I query the both tables separately, it takes just a few seconds.
Linked Severs have terrible performance. Get the data you need to the local server and do the majority of the hard work and processing there instead of a mix of local and remote in a single query.
select remotetab into a temp table
select [key] into #remote_made_local from remotetab
Use the #temp table when doing the where clause filtering and use exists instead of in for better performance
select a.[key], a.name from localtab a where not exists (select 1 from #remote_made_local b where b.[key] = a.[key] )
Vs doing
select [key], name from localtab where key not in (select [key] from #remote_made_local)
There is also a solution without using temporary tables.
By using a left join instead of not in (select ...), you can massively speed up the query. Like this:
select l.key, l.name
from localtab l left join remotetab r on l.key = r.key
where r.key is null ;