This solution may help use an index for iwadisobject and should be a lot quicker than the original DELETE statement. Still, it doesn't prevent NATURAL for businessclassesroot. If there's considerably more records in businessclassesroot than in iwadsobject_lruntimeslot, then an executable stored procedure with something like ('something like' means that I rarely write things like this and that there may well be some errors):

FOR SELECT b.bold_id from iwadisobject_lrutimeslot b
Left join iwadisobject c on c.bold_id = b.objects
Where c.bold_id is null into :MyBoldId
do
delete from businessclassesroot a where a.bold_id = :MyBoldId;

could be considerably faster. Though if iwadisobject_lruntimeslot also contains millions of rows, then this might not be of any help at all.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Hans
Sent: 10. august 2008 22:55
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Speedup delete-statement

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


------------------------------------

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

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