RE: SQL Optimization

It seems every subselect of (select pissnv ... having sum ...) would run
for each of the 5555 rows, to determine if it should be used (not used
5547 times).

If you're on v5r4+, perhaps this would help:
With temp as (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, peis301, temp where pissnv=em# and emstat='Y' and

a.pissnv=temp.pissnv

Assumes pissnv is from table ppcs301. This calculates the 4 rows from
the subselect and joins them to the main query.

One book I've been reading is _The Art of SQL_ by Stephane Faroult
(O'Reilly, 2006). One of the chapters he goes into why separate queries,
each performing very quickly, will perform abysmally when joined
together, as you experienced. Sounds like the optimizer chose a
different way to do things based on statistics and indexes known to it,
possibly even table full scans when it didn't before.

HTH,
Loyd


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Pete Helgren
Sent: Wednesday, January 07, 2009 1:01 PM
To: Midrange Systems Technical Discussion
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