Subject Re: [firebird-support] Re: Request new feature - better perfomance
Author Djordje Radovanovic
Post date 2014-10-21T07:38:30Z
Yes, after first execution I went to store procedure.
 
for select robid,kolicina,rezerva from rprbs where pgod = :i_pgod and sklad = :i_sklad
into :v_robid,:v_kolicina,:v_rbsrezerva
do
begin
  select sum(zahtev - skinuto) from rizhp p, rizah z
        where p.pgod = :i_pgod and p.sklad = :i_sklad and p.robid = :v_robid and
           z.pgod = p.pgod and z.sklad = p.sklad and z.brzah = p.brzah and z.status_ < 3
           into :v_rezerva;
  v_rezerva = iif(v_rezerva is null,0,v_rezerva);
  if (v_rezerva <> v_rbsrezerva) then
    update rprbs set rezerva = :v_rezerva where pgod = :i_pgod and sklad = :i_sklad and robid = :v_robid;
end
 
But problem still exists and optimization improvement could make this query more readable, shorter and of course much faster.
 
Still vote for optimization improvement.
 
Best regards,
 
Djordje Radovanovic
 
Sent: Tuesday, October 21, 2014 9:26 AM
Subject: Re: [firebird-support] Re: Request new feature - better perfomance
 
 

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