Carsten Schäfer wrote:

> Now i want to do a delete:
> delete from T_VERPACKUNGZUORDNUNG v where exists (select 1 from
> T_VERPACKUNG_FAI_ZUORD v2 where v.ID_VERPACKUNGZUORDNUNG =
> v2.F_ID_VERPACKUNGZUORDNUNG and v2.F_ID_APOS = 1000 and
> v2.F_ID_FERTIGUNGSAUFTRAG =1000)
>
> This is very slow, because Firebird is doing a full table scan on both
> tables !
> Plan:
> PLAN (V2 INDEX (PK_VERPACKUNG_FAI_ZUORD))
> PLAN (V NATURAL)
> Adapted plan:
> PLAN (V2 INDEX (PK_VERPACKUNG_FAI_ZUORD)) PLAN (V NATURAL)
>
> When is use a standard IN Statement like:
> DELETE FROM T_VERPACKUNGZUORDNUNG v where v.id_verpackungzuordnung in
> (select v2.f_id_verpackungzuordnung from t_verpackung_fai_zuord v2
> where v2.f_id_apos = 1000 and v2.F_ID_FERTIGUNGSAUFTRAG =1000)
> the plan and the speed is the same.
> I have tried it with Firebird 2.0.3 and version 2.1.0 RC1.
>
> How can i optimize the delete ?
>
> Carsten

Hi Carsten!

It is doing a full table scan on T_VERPACKUNGZUORDNUNG, but using an
index on T_VERPACKUNG_FAI_ZUORD. With a straight delete, there's no way
to avoid this when using only a subselect to decide which rows to delete.

However, you can write a stored procedure with code similar to:

FOR SELECT v2.F_ID_VERPACKUNGZUORDNUNG
WHERE v2.F_ID_APOS = 1000 and v2.F_ID_FERTIGUNGSAUFTRAG =1000
INTO :Variable DO
delete from T_VERPACKUNGZUORDNUNG v
WHERE v.ID_VERPACKUNGZUORDNUNG = :Variable

HTH,
Set