Re: tuff query, Needs to be faster.

From: Brian Ewins <Brian.Ewins_at_gssec.bt.co.uk>
Date: 1997/06/06
Message-ID: <3398130F.5EB0@gssec.bt.co.uk>#1/1

Michael Rothwell wrote:
>
> 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.
>

[snip]

I'd try to get zeros instead of nulls in the table in the first place to avoid calling nvl repeatedly...

select mgrname,drdi,sum(jan),sum(feb),sum(mar),...,sum(jan+feb+mar...) total
from pso_di_detail d,
(

	select c.cost_district,nvl(r.eng_jan,0) - nvl(c.eng_jan,0) jab
        ,   nvl(r.eng_feb,0) - nvl(c.eng_feb,0) feb
        ,   nvl(r.eng_mar,0) - nvl(c.eng_mar,0) mar
		...
	from r_pro r, c_pro c
	where  r.rev_district (+)  = c.cost_district

) x
where
x.cost_district (+) = d.di
and d.rdi = 'USA'
group by d.mgrname,d.drdi
having sum(jan+..+dec)!=0 ;
(this will produce slightly different results - see below)

... or something like this, may at least get rid of some of the duplicate calculations. But these may have been optimised out already. However, it forces you to do the inner calculation for all (700-900) records.

Your big loss here is the '(+)' as I'm sure you know. I don't understand why you want to do this in this query; basically, you *force* the calculation to be done for every district, whether or not they exist in r, or c, then *EXCLUDE* the ones you included with the (+) on c.cost_district (+) = d.di with the having clause! The query I gave above will actually return null rows in this case. The other use of (+) should be left as it is meaningful, but this one should be removed.

	Hope this helps,
		Brian

-- 
****====----                                              Brian Ewins. 
Fax: (44) 141 220 6100                          Tel: (44) 141 220 6121
 "It's time we face reality, my friends...
      We're not exactly rocket scientists." --Gary Larson ----====****

Received on Fri Jun 06 1997 - 00:00:00 CDT