tuff query, Needs to be faster.

From: Michael Rothwell <michael_rothwell_at_non-hp-usa-om46.om.hp.com>
Date: 1997/06/03
Message-ID: <3394A21C.15B11283@non-hp-usa-om46.om.hp.com>#1/1

I have the following query from a legacy sytem that needs to be sped up. I have tried dozens of different ways of writing the query, this is the fastest that I have found so far and it still takes around 30 seconds. Table d only has 2000 records, c about 700, and r about 900. If anyone could help me with this, I would appreciate it.

select d.mgrname,d.drdi,

       sum(nvl(r.eng_jan,0) - nvl(c.eng_jan,0) +
           nvl(r.eng_feb,0) - nvl(c.eng_feb,0) +
           nvl(r.eng_mar,0) - nvl(c.eng_mar,0) +
           nvl(r.eng_apr,0) - nvl(c.eng_apr,0) +
           nvl(r.eng_may,0) - nvl(c.eng_may,0) +
           nvl(r.eng_jun,0) - nvl(c.eng_jun,0) +
           nvl(r.eng_jul,0) - nvl(c.eng_jul,0) +
           nvl(r.eng_aug,0) - nvl(c.eng_aug,0) +
           nvl(r.eng_sep,0) - nvl(c.eng_sep,0) +
           nvl(r.eng_oct,0) - nvl(c.eng_oct,0) +
           nvl(r.eng_nov,0) - nvl(c.eng_nov,0) +
           nvl(r.eng_dec,0) - nvl(c.eng_dec,0) ) total,
       sum(nvl(r.eng_jan,0) - nvl(c.eng_jan,0)) jan,
       sum(nvl(r.eng_feb,0) - nvl(c.eng_feb,0)) feb,
       sum(nvl(r.eng_mar,0) - nvl(c.eng_mar,0)) mar,
       sum(nvl(r.eng_apr,0) - nvl(c.eng_apr,0)) apr,
       sum(nvl(r.eng_may,0) - nvl(c.eng_may,0)) may,
       sum(nvl(r.eng_jun,0) - nvl(c.eng_jun,0)) jun,
       sum(nvl(r.eng_jul,0) - nvl(c.eng_jul,0)) jul,
       sum(nvl(r.eng_aug,0) - nvl(c.eng_aug,0)) aug,
       sum(nvl(r.eng_sep,0) - nvl(c.eng_sep,0)) sep,
       sum(nvl(r.eng_oct,0) - nvl(c.eng_oct,0)) oct,
       sum(nvl(r.eng_nov,0) - nvl(c.eng_nov,0)) nov,
       sum(nvl(r.eng_dec,0) - nvl(c.eng_dec,0)) dec

from r_pro r, c_pro c, pso_di_detail d

where  r.rev_district (+)  = c.cost_district
  and  c.cost_district (+) = d.di
  and  d.rdi = 'USA'

group by d.mgrname,d.drdi

having sum(nvl(r.eng_jan,0) - nvl(c.eng_jan,0) +
           nvl(r.eng_feb,0) - nvl(c.eng_feb,0) +
           nvl(r.eng_mar,0) - nvl(c.eng_mar,0) +
           nvl(r.eng_apr,0) - nvl(c.eng_apr,0) +
           nvl(r.eng_may,0) - nvl(c.eng_may,0) +
           nvl(r.eng_jun,0) - nvl(c.eng_jun,0) +
           nvl(r.eng_jul,0) - nvl(c.eng_jul,0) +
           nvl(r.eng_aug,0) - nvl(c.eng_aug,0) +
           nvl(r.eng_sep,0) - nvl(c.eng_sep,0) +
           nvl(r.eng_oct,0) - nvl(c.eng_oct,0) +
           nvl(r.eng_nov,0) - nvl(c.eng_nov,0) +
           nvl(r.eng_dec,0) - nvl(c.eng_dec,0) ) != 0

Thanks

Michael Received on Tue Jun 03 1997 - 00:00:00 CDT