Thorsten Schöning schrieb am 31.08.2020 um 12:37:
> So for what query size or number of IDs to compare in IN would you
> consider a different approach at all?
In my experience "hundreds" of IDs tend to be quite slow if used with an IN
clause.
Rewriting the IN to a JOIN against a VALUES clause is very often faster:
So instead of:
select *
from t
where id in (1,2,3, .... ,500);
using this:
select *
from t
join (
values (1),(2),(3),...(500)
) as x(id) on x.id = t.id
produces more often than not a more efficient execution plan (assuming no
values are duplicated in the IN list)
Obviously I don't know if such a re-write is even feasible though.
Thomas
