| Subject | Re: [firebird-support] Re: Request new feature - better perfomance |
|---|---|
| Author | Djordje Radovanovic |
| Post date | 2014-10-21T05:57:51Z |
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 sum(thirdthing) from second b where b.something = a.something)
>
> select x.* from (
> select something, anotherthing,
>
>total
> from aWhile all of the suggestions are appropriate for an immediate work around, the OP original point is still valid.
> ) x where x.total > 10
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