Re: AW: SQL Optimization

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