Subject Re: [firebird-support] Re: performance of subselect with group by
Author Björn Reimer

well, that's not the answer I wanted to get, but I've to deal with
that fact.

I've to check wether EXECUTE STATEMENT can execute EXECUTE
BLOCKs, as the DELETE Statement is build dynamically in a proc.

> Hi Björn!
> I don't think there is any simple way to make a delete with a
> subselect as the only part of a where clause perform great on
> largish tables. That is, using EXECUTE BLOCK (which doesn't exist on
> older Firebird versions) should perform OK:
> execute block as
> declare variable id integer;
> begin
> for select min(t.Id) FROM test t
> group by t.reference, t.key
> having count(*) > 1
> into :id do
> delete from test where Id = :id;
> end
> HTH,
> Set


Björn Reimer - RRZE