Re: SQL Optimization

[Pete Helgren <Pete@xxxxxxxxxx>]

Elvis, Lyod and Charles:

Wow!  That was amazing!  Eliminating the "IN" clause was the solution.  
Looking at all three approaches I can see why.  I'll have to remember 
this approach in the future.

Thanks!

Pete


Elvis Budimlic wrote:
> The IN clause with a nested subselect may be the culprit... so try
> converting it into a join if you can.
> Try this and see if it produces the same result set and performs any better:
>
> with recordsIwant 
> (
>   select pissnv 
>   from ppcs301 
>   group by  pissnv 
>   having sum (pipctf)>100 
> )
> select picode,a.pissnv,emlnam,emfnam,emmnam, decimal(pipctf/100,5,2)  as tot
> from ppcs301 a join recordsIwant r using(pissnv) join peis301 on a.pissnv =
> em#
> where emstat='Y'
> order by emlnam,emfnam,emmnam
>
> Hth, Elvis
>
> Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
> OS/400
> www.centerfieldtechnology.com
>
>
> -----Original Message-----
> Subject: 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
>
>
>
>