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

    Post Unanswered: Query Performance Issue

    Our one online transaction has started taking so much time for last couple of days. What is interesting is this started tanking on 4/29, but nothing was changed since 4/17. As there is just 1 SQL statement in the package, the only other thing I can think of is the :HV being passed in may cause a long scan.

    Something has gone awfully wrong in the past 2 days. The CPU and Elapsed time have drastically increased. DBA attempted a rebind to go to a new access path, but it didn’t change the outcome. This one query took close to 90 minutes of cpu time.

    Could anyone please suggesst any changes in query to make it efficient?

    Appreciate your help.

  2. #2
    Join Date
    May 2008
    Posts
    15

    Post query..

    Code:
    SELECT  TABA.SRVDLS_IDN
                     ,TABA.FLAG_CODE
                     ,INDTYP.IND_TYPE_DESC
                  FROM (SELECT SRVDLS.IDN SRVDLS_IDN
                               , 'SLR' FLAG_CODE
                          FROM SERV_DLVRY_SITE SRVDLS
                         WHERE SRVDLS.IDN = :KEYSET-SRVDLS-IDN
                           AND SRVDLS.LOC_EXPLAN_TEXT IS NOT NULL
    
                       UNION
                            
                        SELECT SRVDLS2.IDN SRVDLS_IDN
                              , 'LSP' FLAG_CODE
                          FROM SERV_DLVRY_SITE SRVDLS1
                              ,SERV_DLVRY_SITE SRVDLS2
                         WHERE SRVDLS2.IDN = :KEYSET-SRVDLS-IDN
                           AND SRVDLS2.SRVDLS_IDN = SRVDLS1.IDN
                           AND SRVDLS1.CNCR_CUSTMR_IDN IS NOT NULL
    
                       UNION
    
                        SELECT SERV.SRVDLS_IDN
                              , 'BRS' FLAG_CODE
                          FROM SERVICE SERV
                              ,READING_COND RDGCND
                              ,INSTALLED_REGISTER INSREG
                              ,METER_INSTALLATION MTRINL
                         WHERE SERV.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
                           AND SERV.IDN = RDGCND.SERV_IDN
                           AND SERV.IDN = INSREG.SERV_IDN
                           AND INSREG.MTRINL_IDN = MTRINL.IDN
                           AND INSREG.LAST_EFF_DATE IS NULL
                           AND MTRINL.LAST_EFF_DATE IS NULL
                           AND RDGCND.READING_COND_DATE >=
                               CURRENT DATE - 45 DAYS
                           AND RDGCND.READING_COND_CODE
                                     IN ('004', '005', '010')
    
                       UNION
                                                                               
                        SELECT FWREQ.SRVDLS_IDN
                              , 'PWR' FLAG_CODE
                          FROM FIELD_WORK_REQUEST FWREQ
                         WHERE FWREQ.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
                           AND REQ_STATUS_CODE IN ('OPN', 'REQ')
    
                       UNION
    
                        SELECT FWREQ.SRVDLS_IDN
                              ,'REC' AS FLAG_CODE
                          FROM FIELD_WORK_REQUEST FWREQ
                         WHERE FWREQ.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
                           AND FWREQ.COMPL_DATE IS NULL
                           AND REQ_STATUS_CODE = 'OPN'
                           AND WORK_REQ_TYPE_CODE = '094'
    
                       UNION                                                   
    
                        SELECT FWREQ.SRVDLS_IDN                                
                              ,'RCO' AS FLAG_CODE                              
                          FROM FIELD_WORK_REQUEST FWREQ                        
                         WHERE FWREQ.SRVDLS_IDN = :KEYSET-SRVDLS-IDN           
                           AND FWREQ.COMPL_DATE >=                             
                                     CURRENT DATE - 1 DAY                      
                           AND REQ_STATUS_CODE = 'CLO'                         
                           AND WORK_REQ_TYPE_CODE = '094'                      
                           AND WORK_STATUS_CODE = 'CMPL'                       
                                                                               
                       UNION                                                   
                                                                               
                        SELECT FWREQ.SRVDLS_IDN                                
                              ,'SDI' AS FLAG_CODE                              
                          FROM FIELD_WORK_REQUEST FWREQ                        
                         WHERE FWREQ.SRVDLS_IDN = :KEYSET-SRVDLS-IDN           
                           AND FWREQ.COMPL_DATE >=                             
                                     CURRENT DATE - 1 DAY                      
                           AND REQ_STATUS_CODE = 'CLO'                         
                           AND WORK_REQ_TYPE_CODE = '094'                      
                           AND (WORK_STATUS_CODE IS NULL                       
                            OR WORK_STATUS_CODE = ' ')                         
                                                                               
                       UNION                                                   
                                                                               
                        SELECT FWREQ.SRVDLS_IDN                                
                              ,'RER' AS FLAG_CODE                              
                          FROM FIELD_WORK_REQUEST FWREQ                        
                         WHERE FWREQ.SRVDLS_IDN = :KEYSET-SRVDLS-IDN           
                           AND REQ_STATUS_CODE NOT IN ('CAN', 'CLO')           
                           AND WORK_REQ_TYPE_CODE IN                           
                              ('096', '101', '102')                            
                                                                               
                       UNION                                                   
                                                                               
                        SELECT SERV.SRVDLS_IDN                                 
                              , 'UIM' FLAG_CODE                                
                          FROM SERVICE SERV                                    
                         WHERE SERV.SRVDLS_IDN = :KEYSET-SRVDLS-IDN            
                           AND SERV.INACT_MTR_USG_IND = 'Y'                    
                           AND SERV.STAT_CODE = 'INA'                          
                       UNION                                                   
                                                                               
                        SELECT SITIND.SRVDLS_IDN                               
                              , 'ENP' FLAG_CODE                                
                          FROM SITE_INDICATOR SITIND                           
                         WHERE SITIND.SRVDLS_IDN = :KEYSET-SRVDLS-IDN          
                           AND SITIND.IND_TYPE_CODE = 'EP'                     
                                                                               
                       UNION
    ...continue

  3. #3
    Join Date
    May 2008
    Posts
    15

    query continue...

    Code:
     query continue...
                       
                                SELECT SERV.SRVDLS_IDN
                              ,CASE WHEN MTRINL.EMR_RDG_MTHD_CODE = 'A'
                                    THEN 'AMR'                                 
                                    WHEN MTRINL.EMR_RDG_MTHD_CODE = 'S'
                                    THEN 'AMS'                                 
                                    WHEN MTRINL.EMR_RDG_MTHD_CODE = 'E'
                                    THEN 'ADP'                                 
                                    WHEN MTRINL.EMR_RDG_MTHD_CODE = 'M'
                                    THEN 'MAN'                                 
                                    WHEN MTRINL.EMR_RDG_MTHD_CODE = 'R'
                                    THEN 'AMX'
                                END AS FLAG_CODE
                          FROM SERVICE SERV
                              ,INSTALLED_REGISTER INSREG
                              ,METER_INSTALLATION MTRINL
                         WHERE SERV.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
                           AND INSREG.MTRINL_IDN = MTRINL.IDN
                           AND SERV.IDN = INSREG.SERV_IDN
                          AND
                           VALUE(MTRINL.LAST_EFF_DATE,DATE('9999-12-31'))
                               >= CURRENT DATE 
                           AND 
                           VALUE(INSREG.LAST_EFF_DATE,DATE('9999-12-31')) 
                               >= CURRENT DATE
                           AND MTRINL.EMR_RDG_MTHD_CODE IN
                               ('A', 'S', 'E', 'M','R')
                  UNION 
    
                        SELECT SERV.SRVDLS_IDN
                              ,'ITR' FLAG_CODE
                          FROM SERVICE SERV
                              ,INSTALLED_REGISTER INSREG
                              ,METER_INSTALLATION MTRINL
                         WHERE SERV.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
                           AND INSREG.MTRINL_IDN = MTRINL.IDN
                           AND SERV.IDN = INSREG.SERV_IDN
                          AND
                           VALUE(MTRINL.LAST_EFF_DATE,DATE('9999-12-31'))
                               >= CURRENT DATE 
                           AND
                           VALUE(INSREG.LAST_EFF_DATE,DATE('9999-12-31'))
                               >= CURRENT DATE
                           AND MTRINL.EMR_RDG_MTHD_CODE = 'I'
                           AND MTRINL.COMMUN_TYPE_CODE = ' '
     
                       UNION
    
                        SELECT SERV.SRVDLS_IDN                                 
                              , 'TOU' FLAG_CODE
                          FROM SERVICE SERV
                              ,INSTALLED_REGISTER INSREG
                              ,METER_INSTALLATION MTRINL
                         WHERE SERV.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
                           AND INSREG.MTRINL_IDN = MTRINL.IDN
                           AND SERV.IDN = INSREG.SERV_IDN 
                           AND 
                           VALUE(MTRINL.LAST_EFF_DATE,DATE('9999-12-31')) 
                               >= CURRENT DATE
                           AND
                           VALUE(INSREG.LAST_EFF_DATE,DATE('9999-12-31'))
                               >= CURRENT DATE
                           AND MTRINL.METER_CONFIG_CODE IN ('TOU', 'DTU')
    
                       UNION
                        SELECT SERV.SRVDLS_IDN
                              , 'OMR' FLAG_CODE
                          FROM SERVICE SERV
                              ,INSTALLED_REGISTER INSREG
                              ,METER_INSTALLATION MTRINL
                              ,METER_ROUTE MTRRTE
                         WHERE SERV.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
                           AND INSREG.MTRINL_IDN = MTRINL.IDN
                           AND SERV.IDN = INSREG.SERV_IDN
                           AND
                           VALUE(MTRINL.LAST_EFF_DATE,DATE('9999-12-31'))
                               >= CURRENT DATE
                           AND
                           VALUE(INSREG.LAST_EFF_DATE,DATE('9999-12-31'))
                               >= CURRENT DATE
                           AND MTRINL.EMR_RDG_MTHD_CODE = 'I'
                           AND MTRINL.COMMUN_TYPE_CODE = 'O'
                           AND SERV.MTRRTE_IDN = MTRRTE.IDN 
                           AND MTRRTE.MOBILE_READ_IND ^= 'Y'
    
                       UNION
    
                        SELECT SERV.SRVDLS_IDN
                              , 'MMR' FLAG_CODE
                          FROM SERVICE SERV
                              ,INSTALLED_REGISTER INSREG
                              ,METER_INSTALLATION MTRIN
                              ,METER_ROUTE MTRRTE 
                         WHERE SERV.SRVDLS_IDN = :KEYSET-SRVDLS-IDN            
                           AND INSREG.MTRINL_IDN = MTRINL.IDN                  
                           AND SERV.IDN = INSREG.SERV_IDN                      
                           AND                                                 
                           VALUE(MTRINL.LAST_EFF_DATE,DATE('9999-12-31'))      
                               >= CURRENT DATE                                 
                           AND 
                           VALUE(INSREG.LAST_EFF_DATE,DATE('9999-12-31'))      
                               >= CURRENT DATE
                           AND SERV.MTRRTE_IDN = MTRRTE.IDN 
                           AND MTRRTE.MOBILE_READ_IND = 'Y' 
    
                                                                               
                       UNION
    
                         SELECT DISTINCT INSREG.SRVDLS_IDN
                              , CASE
                                 WHEN MTRINL.RMT_CAPABLE_NM = 'CONNECT'
                                 THEN 'RCC'
                                 WHEN MTRINL.RMT_CAPABLE_NM = 'READ'
                                 THEN 'RRC'
                                END AS FLAG_CODE
                         FROM INSTALLED_REGISTER INSREG
                             ,METER_INSTALLATION MTRINL
                         WHERE INSREG.SRVDLS_IDN =
                              :KEYSET-SRVDLS-IDN
                         AND   INSREG.MTRINL_IDN =
                               MTRINL.IDN
                         AND   VALUE(MTRINL.LAST_EFF_DATE,
                                 DATE('9999-12-31')) >= CURRENT DATE
                         AND   VALUE(INSREG.LAST_EFF_DATE,
                                 DATE('9999-12-31')) >= CURRENT DATE
                         AND   MTRINL.RMT_CAPABLE_NM IN
                                 ('READ','CONNECT')
    
                       UNION
    
                        SELECT SITIND.SRVDLS_IDN
                              , SITIND.IND_TYPE_CODE AS FLAG_CODE
                          FROM SITE_INDICATOR SITIND
                         WHERE SITIND.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
                           AND SITIND.IND_TYPE_CODE NOT IN ('EP', 'HPP')
    
                       UNION
    
                        SELECT Q1.SRVDLS_IDN, Q1.FLAG_CODE FROM (
                          SELECT VALUE(Q2.SRVDLS_IDN, Q3.SRVDLS_IDN)
                          AS SRVDLS_IDN, VALUE(Q2.FLAG, Q3.FLAG)
                          AS FLAG_CODE FROM (
                            (SELECT 'AMI' AS FLAG, SRVDLS.IDN AS SRVDLS_IDN
                               FROM SERV_DLVRY_SITE SRVDLS
                             WHERE SRVDLS.IDN = :KEYSET-SRVDLS-IDN
                               AND EXISTS (
                               SELECT 1 FROM TCBIS_TRF_XREF T
                                 , SERVICE S, METER M
                                WHERE T.SRVDLS_IDN = S.SRVDLS_IDN
                                  AND S.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
                                  AND S.STAT_CODE NOT IN
                                      ('CAN','REQ','RMV','RMW')
                                  AND T.SERV_IDN = S.IDN
                                  AND T.CIRCUIT_NUM IN 
                                      (SELECT CIRCUIT_NUM FROM
                                       UOF_CIRCUIT_PILOT)
                                  AND S.METER_IDN = M.IDN
                                  AND M.FEATURE_CODE IN 
                                      ('L','X','Y') ) ) AS Q2
                          FULL OUTER JOIN
                          (SELECT 'AMA' AS FLAG, SRVDLS.IDN AS SRVDLS_IDN 
                          FROM SERV_DLVRY_SITE SRVDLS
                          WHERE SRVDLS.IDN = :KEYSET-SRVDLS-IDN
                            AND EXISTS (
                               SELECT 1 FROM TCBIS_TRF_XREF T
                                 , SERVICE S, METER M
                                WHERE T.SRVDLS_IDN = S.SRVDLS_IDN
                                  AND S.SRVDLS_IDN = :KEYSET-SRVDLS-IDN
                                  AND S.STAT_CODE NOT IN
                                      ('CAN','REQ','RMV','RMW')
                                  AND T.SERV_IDN = S.IDN
                                  AND T.CIRCUIT_NUM IN
                                      (SELECT CIRCUIT_NUM FROM
                                       UOF_CIRCUIT_PILOT)
                                  AND S.METER_IDN = M.IDN 
                                  AND M.FEATURE_CODE NOT IN 
                                      ('L','X','Y') ) ) AS Q3
                          ON Q2.SRVDLS_IDN = Q3.SRVDLS_IDN ) ) AS Q1
    
                           ) AS TABA
    
                  LEFT OUTER JOIN INDICATOR_TYPE INDTYP
                   ON  TABA.FLAG_CODE = INDTYP.IND_TYPE_CODE
                  QUERYNO 1
               END-EXEC.

  4. #4
    Join Date
    Dec 2008
    Posts
    76
    You give no version or platform, but even if we had these we could not evaluate such a long complex statement properly. It will be dependant on the structure of the tables, the data, and a dozen other factors.

    Get an explain of the statement to pinpoint problem areas. Perhaps it needs indexes, stats, a reorg, etc.. Maybe some parts can be rewritten more efficiently.

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Just two suggestions, from a quick first investigation of the query:

    - try to replace all "UNION"s by "UNION ALL"s
    - the FULL OUTER JOIN: could it be reformulated into an INNER JOIN in this case?

    If you post your PLAN_TABLE after running Explain, maybe some more useful suggestions could be given.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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