Subject Re: Re: [firebird-support] Optimizer request
Author Svein Erling Tysvær
Post date 2016-10-19T12:09:20Z
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 liviuslivius@... [firebird-support] <firebird-support@yahoogroups.com>:


hi,

maybe MERGE is your answer

regards,
Karol Bieniaszewski


-------- Oryginalna wiadomość --------
Od: "ehmmm.firebird@... [firebird-support]" <firebird-support@yahoogroups. com>
Data: 19.10.2016 09:34 (GMT+01:00)
Do: firebird-support@yahoogroups. com
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 setysvar@... [firebird-support] <firebird-support@yahoogroups. com>
Komu: firebird-support@yahoogroups. com
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' softsistem@... [firebird-support] <firebird-support@yahoogroups. com>:


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
 
 



=