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
From: mailto:[email protected]
Sent: Tuesday, October 21, 2014 9:26 AM
To: [email protected]
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 [email protected], <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
From: mailto:[email protected]
Sent: Monday, October 20, 2014 9:29 PM
To: [email protected]
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
