> DELETE FROM X WHERE Y IN( SELECT ....)

The IN SELECT will be executed once for every row in X.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Gary Benade
Sent: Tuesday, August 08, 2006 8:39 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Re: IN clause - using static values vs
table values

> 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 DELETE FROM X WHERE Y IN(1,2,3), but is it more work than in the
first case?

Thanks for your time, much appreciated
Gary




++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item on the
main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links