Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Optimizing query?

Status
Not open for further replies.

beltmanjr

Technical User
Oct 29, 2007
333
NL
HI,
I got a query that needs optimizing, but after reading some articles on optimizing queries I still cant see a manner in which to optimize this query.

The main problem with the query is that it needs to get a max date from a table, grouped by 5 other columns from that table, using the result to determine a price for that group on those max dates.....

Hoping that someone can help me make this query execute a bit faster. The query.
Code:
select ap_relatienr, ap_rekening_soort, ap_rekening_munts, ap_saldo_positie,
ap_symbool, ap_optietype, ap_expiratiedatum, ap_exerciseprijs,
be_symbool, be_optietype, be_expiratiedatum, be_exerciseprijs,
hq.kr_datum, hq.kr_symbool, hq.kr_midden, hq.kr_soort, hq.kr_optietype,
hq.kr_exerciseprijs, hq.kr_expiratiedatum, hq.kr_tijd

from 
(
  epp40bnck.aktuele_posities left outer join   epp40bnck.beleggingsinstrument
  on ap_symbool = be_symbool and
  ap_optietype =  be_optietype and
  ap_expiratiedatum = be_expiratiedatum and
  ap_exerciseprijs = be_exerciseprijs 
) 
left outer join
(
  SELECT mq.kr_datum, mq.kr_symbool, mq.kr_midden,
  mq.kr_soort, mq.kr_optietype, mq.kr_exerciseprijs,
  mq.kr_expiratiedatum, mq.kr_tijd
  FROM epp40bnck.koersen mq JOIN
  (
    SELECT MAX(kr_datum) AS
    sq_datum, kr_symbool, kr_soort, kr_optietype,
    kr_exerciseprijs, kr_expiratiedatum
    FROM epp40bnck.koersen sq
    WHERE kr_soort = 'SLOT'
    GROUP BY kr_symbool, kr_soort, kr_optietype,
    kr_exerciseprijs, kr_expiratiedatum
  )
  sq ON mq.kr_datum = sq_datum
  AND mq.kr_soort = sq.kr_soort
  AND mq.kr_symbool = sq.kr_symbool
  AND mq.kr_optietype = sq.kr_optietype
  AND mq.kr_exerciseprijs = sq.kr_exerciseprijs
  AND mq.kr_expiratiedatum = sq.kr_expiratiedatum
  WHERE mq.kr_soort = 'SLOT'
) hq
on ap_symbool = hq.kr_symbool and
ap_optietype =  hq.kr_optietype and
ap_expiratiedatum = hq.kr_expiratiedatum and
ap_exerciseprijs = hq.kr_exerciseprijs 

where ap_relatienr=ap_ref_relatie 
and ap_relatienr >= 100000
and ap_relatienr < 600000 and ap_saldo_positie <> 0 
and ap_rekening_soort in (0,1000)
 
Start with the basics:

a) have you analyzed all the tables ? Are the stats up to date ?

b) have you run SQL*Trace or explain plan on the query to find its execution plan.
 
I dont have privieleges to analyze the table,
but am aware that the indexes are bad bad bad.

I did run the Explain plan but my understanding is too little to be able to draw conclusions.....

The output of the Explain plan:
Code:
Optimizer Cost Cardinality Bytes Partition Start Partition Stop Partition Id ACCESS PREDICATES FILTER PREDICATES 
SELECT STATEMENT CHOOSE 15499 351 44226      
NESTED LOOPS(OUTER)  15499 351 44226      
NESTED LOOPS(OUTER)  15482 351 36504      
TABLE ACCESS(FULL) EPP40BNCK.AKTUELE_POSITIES ANALYZED 15411 351 14040      
VIEW PUSHED PREDICATE BIBS.TESTVIEW  1 1 64      
NESTED LOOPS  6528 1 82      
VIEW  6527 17357230 624860280      
SORT(GROUP BY)  6527 17357230 624860280      
INDEX(FULL SCAN) EPP40BNCK.A_KR_KEY_FONDS_UK_1 ANALYZED 130538 17357230 624860280      
TABLE ACCESS(BY INDEX ROWID) EPP40BNCK.KOERSEN ANALYZED 1 1 46      
INDEX(RANGE SCAN) EPP40BNCK.A_KR_KEY_FONDS_UK_1 ANALYZED 3 1       
INDEX(UNIQUE SCAN) EPP40BNCK.A_BELEGGINGSINSTRUMENT_PK ANALYZED 1 1 22
 
You should at least be able to check whether the tables have been analyzed by looking in all_tables at the last_analyzed date. Also check figures like the num_rows etc to see if they look reasonably correct.

Have a look at the indexes that are being used in the explain plan. What columns are they on ? Are the selection conditions good ? E.g. if an index is being used for mq.kr_soort = 'SLOT', is this value very selective ? Are there only a few SLOT rows or millions ?
 
I only took a fairly cursory look but a couple of points:

1. Try to use a consistent coding convention. You are mixing case between keywords, apparently random carriage returns, sometimes fully qualifying tables sometimes not, sometimes qualifying columns sometimes not. The overall layout makes it difficult to read and therefore decipher and therefore debug should it be necessary.
2. I think that you could use analytic functions to get the result of your subquery (I could be wrong, as I said, I only took a cursory glance)
Something like:
Code:
MAX(mq.kr_datum) OVER(PARTITION BY  mq.kr_symbool
                                      , mq.kr_midden
                                      , mq.kr_soort
                                      , mq.kr_optietype
                                      , mq.kr_exerciseprijs
                                      , mq.kr_expiratiedatum
                                      , mq.kr_tijd
                          )
I think that it would tie in like:
Code:
SELECT  ap.ap_relatienr
      , ap.ap_rekening_soort
      , ap.ap_rekening_munts
      , ap.ap_saldo_positie
      , ap.ap_symbool
      , ap.ap_optietype
      , ap.ap_expiratiedatum
      , ap.ap_exerciseprijs
      , be.be_symbool
      , be.be_optietype
      , be.be_expiratiedatum
      , be.be_exerciseprijs
      , hq.kr_datum
      , hq.kr_symbool
      , hq.kr_midden
      , hq.kr_soort
      , hq.kr_optietype
      , hq.kr_exerciseprijs
      , hq.kr_expiratiedatum
      , hq.kr_tijd
      , MAX(mq.kr_datum) OVER(PARTITION BY  mq.kr_symbool
                                      , mq.kr_midden
                                      , mq.kr_soort
                                      , mq.kr_optietype
                                      , mq.kr_exerciseprijs
                                      , mq.kr_expiratiedatum
                                      , mq.kr_tijd
                          ) kr_datum
FROM         epp40bnck.aktuele_posities      ap
LEFT JOIN    epp40bnck.beleggingsinstrument  be
ON             ap.ap_symbool         = be.be_symbool 
AND            ap.ap_optietype       = be.be_optietype 
AND            ap.ap_expiratiedatum  = be.be_expiratiedatum 
AND            ap.ap_exerciseprijs   = be.be_exerciseprijs
LEFT JOIN    epp40bnck.koersen                mq 
ON             ap.ap_symbool         = Mq.kr_symbool 
AND            ap.ap_optietype       = Mq.kr_optietype 
AND            ap.ap_expiratiedatum  = Mq.kr_expiratiedatum 
AND            ap.ap_exerciseprijs   = Mq.kr_exerciseprijs
WHERE       ap.ap_relatienr=ap_ref_relatie
AND         ap.ap_relatienr >= 100000
AND         ap.ap_relatienr < 600000 
AND         ap.ap_saldo_positie <> 0
AND         ap.ap_rekening_soort in (0,1000);
Obviously untested.
 
Thanks Jim,
it looks a lot better that way.
Code:
MAX(mq.kr_datum) OVER(PARTITION BY  mq.kr_symbool
                                      , mq.kr_midden
                                      , mq.kr_soort
                                      , mq.kr_optietype
                                      , mq.kr_exerciseprijs
                                      , mq.kr_expiratiedatum
                                      , mq.kr_tijd
                          ) kr_datum
Brings back a bit too much. That's because the kr_tijd (time) differs with the day.

Anyway, I cleaned up the code a bit and am awaiting for an answer from our specialist....
 
Ah, try partinioning on just

kr_symbool, kr_soort, kr_optietype, kr_exerciseprijs, kr_expiratiedatum
See if that works (either that or, well, I guess you know how you want to partition the data better than I :) )
 
Obviously I meant partitioning as opposed to partinioning LOL
 
Although I never knew of that function, it brings back the same issue as with group by, where I need to use the result of this function to find the price on that particular date.

However, our specialist just confirmed that the query is pretty optimal as it comes. But with that KOERSEN table it is doing a full index and some other stuff that makes it horridly slow. We're talking about some million records invloved here on slow systems. :(

However, he informed me that using some temp tables we could fish the data needed only from the tables and then combine this in a query. Since I'm using crystal reports to build a report upon this outcome this would be better anyway. Hope it will fix it.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top