Subject Re: IN clause - using static values vs table values
Author Adam
--- In, "Gary Benade" <gary@...> wrote:
> > 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.

Forget efficiency for a minute.

WHERE ID IN (1,2,3,4,5,6,7,........,1499,1500)

Will not even work, let alone run more efficiently.

For selects with a temporary table containing the record IDs of
interest, you should rather use join than IN. It gives the optimiser
more choice.

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
> 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
> 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?

There are too many variables to give a simple answer. You will need to
perform some experiments with your data shape to see which way
performs better in the real world.

I think you will find that joining is more efficient, but there is a
cost of populating the temporary table that may outweigh its efficiency.