po 31. 8. 2020 v 13:29 odesílatel Thomas Kellerer <shammat@xxxxxxx> napsal:
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.
yes - this query probably will have a slow start, but the execution will be fast. Unfortunately, there are not available statistics.
Thomas
