tuff query, Needs to be faster. 
Author Message
 tuff query, Needs to be faster.

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



Sat, 20 Nov 1999 03:00:00 GMT
 tuff query, Needs to be faster.

Michael,

Have you tried using the EXPLAIN utility ?  It might give you some insight
on how the optimizer is interpreting your sql.  Once you do that, try
using some SQL "hints"  to force the SQL to be executed the most efficient
way.  Last of all,  since you tables are relatively small, the optimizer
might be skipping the indexes you have on your table columns.  If it IS
using the indexes, make sure the data dictionary has good statistics by
running the analyze command on all your tables and indexes.  ( i.e. SQL>
analyze  index <index name> compute statistics )

Cliff  



Sun, 21 Nov 1999 03:00:00 GMT
 tuff query, Needs to be faster.

Done, Done, and Done.

I have tried all of these things that you have mentioned,
with very little improvement.
But thanks for the reply, any other suggestions.

Michael.

Quote:

> Michael,

> Have you tried using the EXPLAIN utility ?  It might give
> you some insight
> on how the optimizer is interpreting your sql.  Once you
> do that, try
> using some SQL "hints"  to force the SQL to be executed
> the most efficient
> way.  Last of all,  since you tables are relatively small,
> the optimizer
> might be skipping the indexes you have on your table
> columns.  If it IS
> using the indexes, make sure the data dictionary has good
> statistics by
> running the analyze command on all your tables and
> indexes.  ( i.e. SQL>
> analyze  index <index name> compute statistics )

> Cliff



Sun, 21 Nov 1999 03:00:00 GMT
 tuff query, Needs to be faster.

Quote:

> 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 ----====****



Tue, 23 Nov 1999 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. A Tuff Tricky One - Please Help with Queries

2. Need Faster Way to Query a Table

3. Slow query execution through JDBC - runs fast in Query Analyzer

4. How tuff is replication on SQL Server

5. Tuff Question

6. TUFF QUESTION - Special Characters????

7. Tuff question : datagrid

8. Here is a tuff one for you

9. I am getting this message when i am tring to export or import anything using

10. error ORA-01855: AM/A.M. or PM/P.M. required

11. Busy Day = Slowdown from 12 AM - 5 AM

12. Use of @am, Am I dumb?


 
Powered by phpBB® Forum Software