Subject Re: [firebird-support] Re: Request new feature - better perfomance
Author
Post date 2014-10-21T07:26:02Z
For such complicated updates, you should create stored procedure, or something like:

execute block as
declare variable :sum;
begin
  select sum(zahtev - skinuto) from rizhp p, rizah z where p.pgod = s.pgod and p.robid = s.robid and z.pgod = p.pgod and z.sklad = p.sklad and z.brzah = p.brzah and z.status_ < 3
  into :sum;

  update rprbs s set rezerva = iif(:sum is null,0, :sum)
  where s.pgod = 2014 and (s.sklad = 1 or s.sklad = 10) and s.rezerva <> iif(:sum is null,0, :sum)
end 



---In firebird-support@yahoogroups.com, <softsistem@...> wrote :

Thanks Sean,
 
this was small example. There could be much more sofisticated and harder examples. For example subqueary with iif() statement almost always result with double select.
For example this query works with no problem but it causes to select same query 4 times.
 
update rprbs s set rezerva = iif((select sum(zahtev - skinuto) from rizhp p, rizah z
where p.pgod = s.pgod and p.robid = s.robid and z.pgod = p.pgod and z.sklad = p.sklad and z.brzah = p.brzah and z.status_ < 3) is null,0,
(select sum(zahtev - skinuto)
  from rizhp p, rizah z where p.pgod = s.pgod and p.robid = s.robid and z.pgod = p.pgod and z.sklad = p.sklad and z.brzah = p.brzah and z.status_ < 3)
  )
where s.pgod = 2014 and (s.sklad = 1 or s.sklad = 10) and s.rezerva <> iif((select sum(zahtev - skinuto) from rizhp p, rizah z
where p.pgod = s.pgod and p.robid = s.robid and z.pgod = p.pgod and z.sklad = p.sklad and z.brzah = p.brzah and z.status_ < 3) is null,0,(select sum(zahtev - skinuto)
from rizhp p, rizah z where p.pgod = s.pgod and p.robid = s.robid and z.pgod = p.pgod and z.sklad = p.sklad and z.brzah = p.brzah and z.status_ < 3))
 
and when your database dealing with big data then your query last for more then few minutes.
 
For me this is important perfomance feature.
 
Best regards,
 
Djordje Radovanovic
 
Sent: Monday, October 20, 2014 9:29 PM
Subject: RE: [firebird-support] Re: Request new feature - better perfomance
 
 


> Try this one:
>
> select x.* from (
>   select something, anotherthing,
>  
(select sum(thirdthing) from second b where b.something = a.something)
>
total
>   from a
> ) x where x.total > 10

While all of the suggestions are appropriate for an immediate work around, the OP original point is still valid.

Firebird engine should be doing a better job of analyzing queries and looking to reduce the unrequired loop/sub-queries due to duplicate sub-queries.


Sean
s