po 31. 8. 2020 v 13:29 odesílatel Thomas Kellerer <shammat@gmx.net> 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.