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?
views:
82answers:
3
+2
A:
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;
ammoQ
2010-08-23 07:09:54
+1. Exactly. I would expect this query to take as much time as the SELECT * without the condition. What do you mean by slow? Slower than the full select?
Thilo
2010-08-23 07:15:38
yeah slower then a select * witout any condition. the normal select * takes like 1 second. select * where id not in (sub select [which is fast by itself]) takes more then 30 seconds. the total number of rows in the table is 12734
Karim
2010-08-23 07:38:06
thanks for the examples. actually i will try them but i was using alternative 3 and it was fast but when i tried to outer join on 2 tables then it started to be very slow (more then 30 seconds). the strange thing is that i think the "where id not in" query should be fast to execute even on a table scan, i dont understand why its so slow on sql server ce. and the strange thing is that i didnt find any information about that slowliness on the internet, am i the only one who is trying to use "id not in(select)" on sql server ce in the whole world?
Karim
2010-08-23 18:51:39
You did not mention the sub select before....
Thilo
2010-08-24 01:49:14
A:
This is not a problem in SQL Server CE, but overall database.
The OPERATION IN is sargable and NOT IN is nonsargable.
What this mean ?
Search ARGument Able, thies mean that DBMS engine can take advantage of using index, for Non Search ARGument Ablee the index can't be used.
The solution might be using filter statement to remove those IDs
More in SQL Performance Tuning by Peter Gulutzan.
Vash
2010-08-23 07:17:02
i cant use a filter to remove these idsi tried left outer join and it was fast but when i joined 2 tables then it became slow.
Karim
2010-08-23 07:41:06
A:
ammoQ is right, index does not help much with your query. Depending on distribution of values in your ID column you could optimise the query by specifying which IDs to select rather than not to select. If you end up requesting say more than ~25% of the table index will not be used anyway though because for nonclustered indexed (which is the only type of indexes which SQL CE supports if memory serves) it would be cheaper to scan the table. Otherwise (if the query is actually selective) you could re-write query with ID ranges to select ('union all' may work better than 'or' to combine ranges if SQL CE supports 'union all', not sure)
vaso
2010-08-23 07:30:05