Try maybe something like

delete from businessclassesroot a where
exists (select 1 from iwadisobject_lrutimeslot b where b.bold_id = a.bold_id
and not exists (select 1 from iwadisobject c where c.bold_id = b.objects)

The 'not in' clause can be expensive

----- Original Message -----
From: "swestner" <s@...>
To: <firebird-support@yahoogroups.com>
Sent: Sunday, August 10, 2008 2:09 PM
Subject: [firebird-support] Speedup delete-statement


> Hello,
>
> does anybody know if there is an alternative to the following select:
>
> delete from businessclassesroot where bold_id in
> (select bold_id from iwadisobject_lrutimeslot where objects not in
> (select bold_id from iwadisobject))
>
> It runs about an hour on a DB which have 30.000.000 entries in
> businessclassesroot and iwadisobject.
>
> The actual plan is:
>
> PLAN (IWADISOBJECT NATURAL)
> PLAN (IWADISOBJECT_LRUTIMESLOT INDEX (IX_IWADISOBJECT_LRUTIMESL09I))
> PLAN (BUSINESSCLASSESROOT NATURAL)
>
> There are indexes on ale columns which are used in the SQL.
>
> Thanks
>
> Stefan
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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
>
>
>