Re: AW: SQL Optimization

[Pete Helgren <Pete@xxxxxxxxxx>]

Thanks Birgitta.  Your version produced a different set of results but I 
haven't had the chance to explore why.  I will use the iNav visual 
explain just to take a look at what is going on.

Pete

Birgitta Hauser wrote:
> Hi,
>
> first I'd rewrite the query as follows:
> With x as (Select Pissnv as xPissnv 
>              from ppcs301
>              Group By pissnv
>              Having sum(pipctf) > 100)
> select picode, pissnv, emlnam, emfnam, emmnam, 
>       decimal(pipctf/100,0 , 5, 2) as tot
>  from ppcs301 join peis301 on pissnv=em# and emstat = 'Y'
>               join x on xPissNv = xPissnv
> Order By emlnam, emfnam, emmnam
>
> If the query is executed by the old CQE, the query optimizer may execute the
> join in the from clause first and after execute the sub-select in the where
> clause.
> Wenn using a Common Table Expression and joining the result in the from
> clause, the optimizer may change the sequence of the execution joins and
> sub-queries, if it may be faster. Instead of using a Common Table Expression
> you may execute the sub-select in the from clause which may lead to the same
> access path as using a CTE.
>
> After make sure, that there are indexes (or keyed logical file built over
> the join fields and over emstat 'Y')
> It may also help to have an index (or keyed logical file built over the
> order by fields), or better an index over em#, emstat, emlanm, emfnam,
> emmnam.
>
> If you are executing your query from green screen, just execute the
> CL-command STRDBG before executing STRSQL and look at the joblog after the
> execution.
> If indexes are proposed, you'll find it in the joblog.
>
> A better way is to use iNavigator's Run an SQL Script's Visual Explain (and
> Index Advisor) to see if and which indexes are missed.
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
>
>
> -----Ursprüngliche Nachricht-----
> Von: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Pete Helgren
> Gesendet: Wednesday, 07. January 2009 20:01
> An: Midrange Systems Technical Discussion
> Betreff: SQL Optimization
>
> I have run into a situation where two sub second queries, when combined, 
> became a 10 -20 minute monster and I am not sure why.
>
> Here is the SQL statement being executed:
>
> select picode,pissnv,emlnam,emfnam,emmnam, decimal(pipctf/100,5,2)
>  as tot from ppcs301,peis301 where pissnv=em# and
>  emstat='Y' and pissnv in(select pissnv from ppcs301 group by
>  pissnv having sum (pipctf)>100 ) order by emlnam,emfnam,emmnam
>
> This query runs in less than a second and produces 5555 records:
>
> select picode,pissnv,emlnam,emfnam,emmnam, decimal(pipctf/100,5,2)
>  as tot from ppcs301,peis301 where pissnv=em# and
>  emstat='Y'
>  order by emlnam,emfnam,emmnam 
>
> This query (the subselect) runs in less than a second and produces 4 
> records:
>
> select pissnv from ppcs301 group by
>  pissnv having sum(pipctf)>100
>
> When combined (as in the first statement) it will take 10 - 20 minutes 
> to run, process millions of records and produce 8 results (which is 
> correct).
>
> Obviously, I am doing something wrong because I assumed the result of 
> these two queries would also be sub-second to run.  What did I do wrong?
>
> Thanks.
>
> Pete Helgren
>
>
>