Link to home
Start Free TrialLog in
Avatar of PHILIP233
PHILIP233

asked on

Query Tuning

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.
Avatar of Jagdish Devaku
Jagdish Devaku

hi,

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

please check it...

all the best...
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...
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.

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

ASKER CERTIFIED SOLUTION
Avatar of gajmp
gajmp

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial