| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
02-02-09, 11:46
|
|
Registered User
|
|
Join Date: May 2008
Posts: 15
|
|
Query Performance Issue
|
|
We found out one bad query in one of the Cobol Access module. Our DBA said - "The code in red needs to be modified so the where clause includes CUSACC_IDN or move it to the ‘on clause’. This query runs every minute on average." It would be appreciated if you let me know what needs to be done in this..
Here is the query:-
Code:
SELECT
TABB.DPASCD_IDN
,TABB.MONETARY_TMSTMP
,TABB.DPA_PAID_DLRS
,TABB.CUSACC_IDN
,TABB.PMT_DUE_DATE
,VALUE(TABB.PMT_DUE_DLRS, 0)
,VALUE(TABB.REMINDER_DATE, '1900-01-01')
,TABB.BILL_MONTH
,VALUE(TABB.REM_REVIEW_DATE,'1900-01-01')
,VALUE(TABB.DIS_REVIEW_DATE,'1900-01-01')
,CASE WHEN MAX(AGRMT.CUSACC_IDN) IS NULL
THEN NULL
ELSE TABB.CUT_NOTICE_DATE
END AS CUT_NOTICE_DATE
FROM
(SELECT TABA.IDN
,TABA.DPASCD_IDN
,TABA.MONETARY_TMSTMP
,TABA.DPA_PAID_DLRS
,TABA.CUSACC_IDN
,TABA.CUT_NOTICE_DATE
,TABA.PMT_DUE_DATE
,TABA.PMT_DUE_DLRS
,TABA.REMINDER_DATE
,TABA.BILL_MONTH
,CASE WHEN DPAQUE.DPA_ACTION_CODE = 'REM'
THEN DPAQUE.DPA_REVIEW_DATE
ELSE NULL
END AS REM_REVIEW_DATE
,CASE WHEN DPAQUE.DPA_ACTION_CODE = 'DIS'
THEN DPAQUE.DPA_REVIEW_DATE
ELSE NULL
END AS DIS_REVIEW_DATE
FROM (SELECT DPAPLN.IDN
,DPASCH.IDN AS DPASCD_IDN
,DPAPLN.MONETARY_TMSTMP
,DPAPLN.DPA_PAID_DLRS
,DPAPLN.CUSACC_IDN
,DPASCH.CUT_NOTICE_DATE
,DPASCH.PMT_DUE_DATE
,DPASCH.PMT_DUE_DLRS
,CASE WHEN DPASCH.REM_SENT_DATE >=
DPASCH.VRBL_REM_DATE
OR DPASCH.VRBL_REM_DATE IS NULL
THEN DPASCH.REM_SENT_DATE
ELSE DPASCH.VRBL_REM_DATE
END AS REMINDER_DATE
,DPASCH.BILL_MONTH
FROM DEFERRED_PMT_PLAN DPAPLN
,DPA_PMT_SCHED DPASCH
WHERE DPAPLN.IDN = ?
AND DPAPLN.IDN = DPASCH.DPAPLN_IDN) AS TABA
LEFT OUTER JOIN DPA_REVIEW_QUEUE DPAQUE
ON TABA.DPASCD_IDN = DPAQUE.DPASCD_IDN
) AS TABB
LEFT OUTER JOIN (SELECT A.CUSACC_IDN
FROM CUSTOMER_AGREEMENT A
,UTIL_SERV_AGRMT B
WHERE A.AGRMT_TYPE_CODE = 'UTL'
AND B.STAT_CODE IN ('OPN','PFB','PDT')
AND A.IDN = B.CUSAGR_IDN
AND B.DISCON_STAT_CODE IN ('PNPDE','PNPDP')
) AS AGRMT
ON AGRMT.CUSACC_IDN = TABB.CUSACC_IDN
LEFT OUTER JOIN DPA_PMT_SCHED DPASCD
ON DPASCD.DPAPLN_IDN = TABB.IDN
AND DPASCD.PMT_DUE_DATE <= CURRENT DATE
GROUP BY TABB.IDN
,TABB.DPASCD_IDN
,TABB.MONETARY_TMSTMP
,TABB.DPA_PAID_DLRS
,TABB.CUSACC_IDN
,TABB.CUT_NOTICE_DATE
,TABB.PMT_DUE_DATE
,TABB.PMT_DUE_DLRS
,TABB.REMINDER_DATE
,TABB.BILL_MONTH
,TABB.REM_REVIEW_DATE
,TABB.DIS_REVIEW_DATE
ORDER BY TABB.IDN
,TABB.PMT_DUE_DATE
,TABB.DPASCD_IDN
,TABB.REM_REVIEW_DATE
,TABB.DIS_REVIEW_DATE
|
|
02-03-09, 17:03
|
|
Registered User
|
|
Join Date: May 2008
Posts: 15
|
|
I have modified the code (which are in red in my first post) a little bit. Please let me know whether or not I am right.
Code:
LEFT OUTER JOIN (SELECT A.CUSACC_IDN
FROM CUSTOMER_AGREEMENT A
,UTIL_SERV_AGRMT B
WHERE A.CUSACC_IDN = TABB.CUSACC_IDN
AND A.AGRMT_TYPE_CODE = 'UTL'
AND B.STAT_CODE IN ('OPN','PFB','PDT')
AND A.IDN = B.CUSAGR_IDN
AND B.DISCON_STAT_CODE IN ('PNPDE','PNPDP')
) AS AGRMT
ON AGRMT.CUSACC_IDN = TABB.CUSACC_IDN
|
|
02-03-09, 17:53
|
|
Registered User
|
|
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
|
|
|
|
you could try making the whole thing a bit simpler. Since you aren't doing any summations or other encapsulation along the way, there is no need for all of the table expressions. You could make the statement much simpler looking with something like the following:
SELECT ALL COLUMNS NEEDED -- IN THE CASE STATEMENTS YOU CAN DO ELSE THE DATE YOU WANT, INSTEAD OF ELSEING THEM TO NULL AND THEN USING THE VALUES CLAUSE....
FROM DEFERRED_PMT_PLAN DPAPLN
INNER JOIN DPA_PMT_SCHED DPASCH
ON DPAPLN.IDN = DPASCH.DPAPLN_IDN
LEFT OUTER JOIN DPA_REVIEW_QUEUE DPAQUE
ON DPAPLN.IDN = DPAQUE.DPASCD_IDN
LEFT OUTER JOIN CUSTOMER_AGREEMENT A
INNER JOIN UTIL_SERV_AGRMT B
ON B.STAT_CODE IN ('OPN','PFB','PDT')
AND A.IDN = B.CUSAGR_IDN
AND B.DISCON_STAT_CODE IN ('PNPDE','PNPDP')
ON A.AGRMT_TYPE_CODE = 'UTL'
AND A.CUSACC_IDN = DPAPLN.CUSACC_IDN
LEFT OUTER JOIN DPA_PMT_SCHED DPASCD
ON DPASCD.DPAPLN_IDN = DPAPLN.IDN
AND DPASCD.PMT_DUE_DATE <= CURRENT DATE
WHERE DPAPLN.IDN = ?
Dave
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Query Performance Issue
