--- In firebird-support@yahoogroups.com, "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.

SELECT *
FROM TABLE
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

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

DELETE FROM

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

Adam