Sometimes I use (a mixture of selectable and executable) EXECUTE BLOCK (you
don't need changed_records, I just tend to prefer knowing whether I changed
100 or 2 million rows):
execute block returns (changed_records integer) as
declare variable OID integer;
begin
changed_records = 0;
for select distinct o.id
from orders o
join partners p on o.partid = p.partid
where o.delivery_date is null
and p.country='Spain'
into :oid do
begin
update orders
set something = 'something other'
where id = :oid;
changed_records = changed_records + row_count;
end
suspend;
end
Of course, you need to commit afterwards...
HTH,
Set
2016-10-19 10:46 GMT+02:00 liviuslivius [email protected]
[firebird-support] <[email protected]>:
>
>
> hi,
>
> maybe MERGE is your answer
>
> regards,
> Karol Bieniaszewski
>
>
> -------- Oryginalna wiadomość --------
> Od: "[email protected] [firebird-support]" <
> [email protected]>
> Data: 19.10.2016 09:34 (GMT+01:00)
> Do: [email protected]
> Temat: Re: [firebird-support] Optimizer request
>
>
>
> I'm continuing in old conversation because I have similar question.
>
>
> How to avoid using IN(subselect) in UPDATE?
>
>
> Theoretical example:
>
>
> update orders o
> set o.something = 'something other'
> where o.delivery_date is null
> and o.partid in (select p.partid from partners p where p.country='Spain')
>
>
> I'm using FB 2.5.x
>
> Right now I don't have big real data for testing.
> I'm just wondering because from what I understand from here then for every
> row of orders firebird would make that constant subselect again and again.
> Am I right?
>
> Would using EXISTS() help?
>
>
> E.
>
>
> ---------- Původní zpráva ----------
> Od: Svein Erling Tysvær [email protected] [firebird-support] <
> [email protected]>
> Komu: [email protected]
> Datum: 9. 9. 2016 17:10:21
> Předmět: Re: [firebird-support] Optimizer request
>
>
>
> Never use IN (subselect). Change to
>
> select * from orders where exists( select * from partners where
> partners.partid = orders.partid and partners.country = ‘Spain’)
>
> 2016-09-09 14:30 GMT+02:00 'Djordje Radovanovic' [email protected]
> [firebird-support] <[email protected]>:
>
>
>
> I tried query with subquery in where clause and found big issue for this
> type of subquery.
>
> for example:
>
> select * from orders where orders.partid in (select partners.partid from
> partners where partners.country = ‘Spain’)
>
> Perfomance Analysis returns me this
>
> partners 687660 non index reads
> orders 28657 index reads
>
> If you analyze this result you’ll find that there is 687659 unnecessary
> non index reads. If developer of optimizer accept that all queries on the
> left side of where clouse has priority (and there is no way to be opposite)
> than we have big improvement in optimization.
>
> Best regards,
>
> Djordje Radovanovic
>
>
>
>
>
> =
>
>
>
>
>
