Subject RE: [firebird-support] Re: IN clause - using static values vs table values
Author Alan McDonald
> > Adam wrote,
> > A couple of hundred is OK, although it hits the index for EACH member.
> > You will have problems if you try with 1500 though.
> > It does not make sense to delete from a join. You can do
> > delete from sometable where ID in (select ID from sometable join ....)
> Adam, sorry if I keep hammering on the same point, but say I had
> 1500 items
> in my list. Would it be more efficient to use a hard coded IN
> clause where
> the index gets hit once per item, or would it be better to use the DELETE
> FROM X WHERE Y IN( SELECT ....) method.
> I would assume that firebird would have to do more work in the
> second case
> by having to generate the list that I would have supplied as part of the
> query, and in the process also generating index hits. I guess there is
> additional work involved in parsing the IN clause in the case of
> X WHERE Y IN(1,2,3), but is it more work than in the first case?
> Thanks for your time, much appreciated
> Gary

if you have an admin tool like IBExpert (or several others), the plan
analyzer will give you chapter and verse about the index reads and query
efficiency. Test it yourself to get the full picture.