Results 1 to 3 of 3
  1. #1
    Join Date
    May 2008
    Posts
    15

    Post Unanswered: 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

  2. #2
    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

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •