Subject Re: [firebird-support] update with subselect, speed / optimization (data repair)
Author Svein Erling Tysvaer
Hi Philip!

At 01:04 22.07.2003 +0100, you wrote:
>update transactions set transactions.tax_entity_id = (select
>bills.actual_tax_id from bills where
> = transactions.bill_id) where transactions.tax_entity_id =
>(select bills.provider_tax_id
>from bills where = transactions.bill_id);

I'll assume this SQL executes correctly. Try changing to

update transactions
set transactions.tax_entity_id =
(select B1.actual_tax_id from bills B1
where = transactions.bill_id)
where EXISTS
(select * from bills B2
where transactions.tax_entity_id = B2.provider_tax_id
AND = transactions.bill_id);


Your problem is that you have two tables referenced using no index (one
such table is no big deal, two multiplies the problems - though in your
case only by a factor of considerably less than 31729). I don't know
whether this is due to not aliasing the two occurences of bills or if it is
because of your subselect, but in general I find using WHERE <field> =
(<SUBSELECT>) to be very slow (at least in Firebird 1.0, I don't know about
the 'Arno' of Firebird 1.5).

Please report back your results - and the new plan - so that we can know
how to help others in the future,