Solved

Query Tuning

Posted on 2008-06-24
8
546 Views
Last Modified: 2013-12-18
SELECT DISTINCT BR.CUSTOMERCODE,
                DECODE(CUS.CUSTOMERTYPENUM,
                       1,
                       CUS.LASTNAME || ' ' || CUS.FIRSTNAME || ' ' ||
                       CUS.MIDDLENAME,
                       2,
                       CUS.COMPANYNAME,CUS.COMPANYNAME) CUSTOMERNAME,
                BR.BILLCURRENCYCODE CURRENCYCODE,
                BR.FRANCHISECODE FRANCHISE,
                DECODE(I.ITEMDESC, NULL, BR.VARIANTCODE, I.ITEMDESC) VARIANTDESCRIPTION,
                BR.VIN VIN,
                SN.MODELYEAR MY,
                SUM(BRD.BILLSAMOUNTTC) OVER(PARTITION BY BRD.BILLSREGISTERID) SETAMOUNT,
                SUM(BRD.INTERESTAMOUNTTC) OVER(PARTITION BY BRD.BILLSREGISTERID) INTAMT,
                (SUM(BRD.BILLSAMOUNTTC)
                 OVER(PARTITION BY BRD.BILLSREGISTERID) +
                 SUM(BRD.INTERESTAMOUNTTC)
                 OVER(PARTITION BY BRD.BILLSREGISTERID)) SETNET,
                BRD.BILLDUEDATE,
                BR.CURRENTSETNUMBER SETNUMBER,
                (SELECT SUM(BRD.BILLSAMOUNTTC)
                   FROM BILLSREGISTERDETAILS BRD1
                  WHERE BR.BILLSREGISTERID = BRD1.BILLSREGISTERID
                    AND (((BRD1.BILLDUEDATE <= '&PDATE') AND
                        (BRD1.SETTLEMENTDATE > '&PDATE' or
                        BRD1.SETTLEMENTDATE is null)))
                  GROUP BY BRD1.BILLSREGISTERID) OVERDUEAMOUNT,
                (SELECT SUM(BRD2.BILLSAMOUNTTC)
                   FROM BILLSREGISTERDETAILS BRD2
                  INNER JOIN BILLSREGISTER BR2 ON BR2.BILLSREGISTERID =
                                                  BRD2.BILLSREGISTERID
                                              AND ((BRD2.BILLDUEDATE >
                                                  '&PDATE' AND
                                                  (BRD2.SETTLEMENTDATE >
                                                  '&PDATE' OR
                                                  BRD2.SETTLEMENTDATE IS NULL)))
                                              AND BR2.CURRENTSETDATE <=
                                                  '&PDATE'
                  WHERE BR2.BILLSREGISTERID = BR.BILLSREGISTERID
                  GROUP BY BRD2.BILLSREGISTERID) UNDUEAMOUNT,
                TO_CHAR(BR.CURRENTSETDATE, 'DD-MON-YYYY') SETDATE,
                (BR.TOTALLOANAMOUNT / BR.EXCHANGERATE) LOANAMOUNT,
                TO_CHAR(MIN(BRD.BILLDUEDATE)
                        OVER(PARTITION BY BRD.BILLSREGISTERID),
                        'DD-MON-YYYY') FIRSTDUEDATE,
                TO_CHAR(MAX(BRD.BILLDUEDATE)
                        OVER(PARTITION BY BRD.BILLSREGISTERID),
                        'DD-MON-YYYY') LASTDUEDATE,
                BRD.BILLSAMOUNTTC,
                TO_CHAR(BRD.SETTLEMENTDATE, 'MON,YYYY') PAYMENTDATE,
                BRD.SETTLEMENTDATE BILLDUEDATE
  FROM BILLSREGISTER BR
  INNER JOIN BILLSREGISTERDETAILS BRD ON BR.BILLSREGISTERID = BRD.BILLSREGISTERID
  INNER JOIN CUSTOMER CUS ON CUS.CUSTOMERCODE = BR.CUSTOMERCODE
  LEFT OUTER JOIN ITEM I ON I.ITEMCODE = BR.VARIANTCODE
  LEFT OUTER JOIN SERIALNUMBER SN ON SN.VIN = BR.VIN
  WHERE BRD.BILLDUEDATE <= '&PDATE'
  ORDER BY BRD.BILLDUEDATE

Kindly verigy the above query.
Kindly suggest something to improve the performance.
0
Comment
Question by:PHILIP233
8 Comments
 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 21853050
hi,

creating indexes on the tables will improve the performance...

please check it...

all the best...
0
 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 21853564
I would like to know the reason to delete this quetion... I am already working on this issue... if there is no valid reason he should distribute the points...
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 21853990
It is a very vague question and the only answer given so far is a very vague answer. I don't see a problem deleting.

It would be more helpful if the author posted some of the table DDL, gave some examples of how it is poorly performing and the main business goal of the query.

It is also listed as a SQL Server and Oracle question but looking at what the query is doing, it is obviously written in PL/SQL and is really an Oracle question.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 21857643

Actually it's SQL*Plus (and badly written).

0
 
LVL 3

Accepted Solution

by:
gajmp earned 125 total points
ID: 21863857
try this one.... if this also give perf probs then join the global temp table temp_overdueamnt and temp_undueamount in the from clasuse..instead of creating inline view for the same... If that also not satified then post the explain plan of ur original SQL..


-- CREATE GLOBAL TEMP TABLE FOR OVERDUEAMOUNT (ONE TIME EXECUTION)  
CREATE GLOBAL TEMPORARY TABLE temp_overdueamount (
  billsregisterid  datatype,
  overdueamount  datatype
) ON COMMIT DELETE ROWS;

-- CREATE GLOBAL TEMP TABLE FOR UNDUEAMOUNT (ONE TIME EXECUTION)
CREATE GLOBAL TEMPORARY TABLE temp_undueamount (
  billsregisterid  datatype,
  undueamount  datatype
) ON COMMIT DELETE ROWS

-- BEFORE EXECUTING UR SQL INSERT ROW INTO ABOVE TWO TEMP TABLE (THIS SHOULD BE EXECUTED BEFOR WHEN EVER UR EXECUTING UR SQL)  
INSERT INTO temp_overdueamount
SELECT brd1.billsregisterid, SUM (brd.billsamounttc)
FROM billsregisterdetails brd1
WHERE (((brd1.billduedate <= '&PDATE') AND (brd1.settlementdate > '&PDATE' OR brd1.settlementdate IS NULL )))
GROUP BY brd1.billsregisterid;

INSERT INTO temp_undueamount
SELECT br2.billsregisterid, SUM (brd2.billsamounttc)
FROM billsregisterdetails brd2 INNER JOIN billsregister br2 ON br2.billsregisterid = brd2.billsregisterid
AND (( brd2.billduedate >'&PDATE' AND ( brd2.settlementdate > '&PDATE' OR brd2.settlementdate IS NULL ) ))
AND br2.currentsetdate <= '&PDATE'
GROUP BY br2.billsregisterid

-- UR MODIFIED SQL
SELECT DISTINCT br.customercode,
         DECODE (cus.customertypenum, 1, cus.lastname||' '||cus.firstname||' '||cus.middlename, 2, cus.companyname, cus.companyname ) customername,
         br.billcurrencycode currencycode,
         br.franchisecode franchise,
         DECODE (i.itemdesc, NULL, br.variantcode, i.itemdesc) variantdescription,
       br.vin vin,
         sn.modelyear my,
       SUM (brd.billsamounttc) OVER (PARTITION BY brd.billsregisterid) setamount,
         SUM (brd.interestamounttc) OVER (PARTITION BY brd.billsregisterid) intamt,
       ( SUM (brd.billsamounttc) OVER (PARTITION BY brd.billsregisterid) + SUM (brd.interestamounttc) OVER (PARTITION BY brd.billsregisterid)) setnet,
         brd.billduedate,
         br.currentsetnumber setnumber,
         (SELECT overdueamount
          FROM temp_overdueamount o
            WHERE br.billsregisterid = o.billsregisterid ) overdueamount,
         (SELECT overdueamount
          FROM temp_undueamount u
            WHERE br.billsregisterid = u.billsregisterid ) undueamount,
            TO_CHAR (br.currentsetdate, 'DD-MON-YYYY') setdate,
        (br.totalloanamount / br.exchangerate) loanamount,
        TO_CHAR(MIN (brd.billduedate) OVER (PARTITION BY brd.billsregisterid),'DD-MON-YYYY') firstduedate,
        TO_CHAR(MAX (brd.billduedate) OVER (PARTITION BY brd.billsregisterid),'DD-MON-YYYY') lastduedate,
        brd.billsamounttc,
        TO_CHAR (brd.settlementdate, 'MON,YYYY') paymentdate,
        brd.settlementdate billduedate
FROM billsregister br INNER JOIN billsregisterdetails brd
       ON br.billsregisterid = brd.billsregisterid INNER JOIN customer cus
       ON cus.customercode = br.customercode LEFT OUTER JOIN item i
       ON i.itemcode = br.variantcode LEFT OUTER JOIN serialnumber sn
       ON sn.vin = br.vin
WHERE brd.billduedate <= '&PDATE'
ORDER BY brd.billduedate
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now