Why the Select * FROM Table where ID NOT IN ( list of int ids) query is slow in sql server ce?

Question

well this problem is general in sql server ce
i have indexes on all the the fields.
also the same query but with ID IN ( list of int ids) is pretty fast.
i tried to change the query to OUTER Join but this just make it worse. so any hints on why this happen and how to fix this problem?

Answer 1

That's because the index is not really helpful for that kind of query, so the database has to do a full table scan. If the query is (for some reason) slower than a simple "SELECT * FROM TABLE", do that instead and filter the unwanted IDs in the program.

EDIT: by your comment, I recognize you use a subquery instead of a list. Because of that, there are three possible ways to do the same (hopefully one of them is faster):

Original statement:

select * from mytable where id not in (select id from othertable);

Alternative 1:

select * from mytable where not exists 
   (select 1 from othertable where mytable.id=othertable.id);

Alternative 2:

select * from mytable
minus
select mytable.* from mytable in join othertable on mytable.id=othertable.id;

Alternative 3: (ugly and hard to understand, but if everything else fails...)

select * from mytable
  left outer join othertable on (mytable.id=othertable.id)
  where othertable.id is null;