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
=
