Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    54

    Unanswered: Help speed up query?

    Each table has about 80,000 records each and the query is taking ~15mins on a very fast zOS mainframe.

    Any hints on making this a tad faster?

    Code:
    INSERT INTO TOTDB01.PROD_ABEND_CRITL_BAT         
    SELECT                                           
      SUBSTR(PROD_ABEND.JOB_TXT,1,6)                 
        -- CRITL_JOB_TXT,                            
     , INT(600000000) + PROD_ABEND.PROD_ABEND_ID     
        -- PROD_ABEND_CRITL_BAT_ID,                  
     , PROD_ABEND.DT_DT                              
        -- DT_DT,                                    
     , PROD_ABEND.TM_TXT                             
        -- TM_TXT,                                   
     , PROD_ABEND.SYS_ID_TXT                         
        -- SYS_IDENTIFER_TXT,                        
     , PROD_ABEND.JOB_TXT                            
        -- JOB_TXT,                                  
     , CRITL_BAT.PROC_NM_TXT                         
        -- PROC_NM_TXT,                              
     , PROD_ABEND.PGM_TXT                            
        -- PGM_TXT,                                              
     , PROD_ABEND.APPLCTN_TXT                                    
        -- APPLCTN_TXT,                                          
     , PROD_ABEND.STEP_NM_TXT                                    
        -- STEP_NM_TXT,                                          
     , PROD_ABEND.PGM_NM_TXT                                     
        -- PGM_NM_TXT,                                           
     , PROD_ABEND.TERM_CD_TXT                                    
        -- TERM_CD_TXT                                           
     FROM                                                        
       TOTDB01.CRITL_BAT CRITL_BAT INNER JOIN                    
         TOTDB01.PROD_ABEND PROD_ABEND                           
         ON CRITL_BAT.PROC_NM_TXT = SUBSTR(PROD_ABEND.JOB_TXT,1,6)
     WHERE                                                       
       PROD_ABEND.JOB_TXT = CRITL_BAT.PROC_NM_TXT AND            
     CRITL_BAT.SCHDG_SYS_TXT = 'ZEKE';                                                     
          COMMIT;

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    goldfishhh, this might be part of the problem:
    Code:
     FROM                                                        
       TOTDB01.CRITL_BAT CRITL_BAT 
         INNER JOIN                    
       TOTDB01.PROD_ABEND PROD_ABEND                           
         ON CRITL_BAT.PROC_NM_TXT = SUBSTR(PROD_ABEND.JOB_TXT,1,6)
     WHERE                                                       
           PROD_ABEND.JOB_TXT = CRITL_BAT.PROC_NM_TXT 
       AND CRITL_BAT.SCHDG_SYS_TXT = 'ZEKE';
    I don't understand why you have this in the ON predicate
    Code:
    CRITL_BAT.PROC_NM_TXT = SUBSTR(PROD_ABEND.JOB_TXT,1,6)
    and this in the WHERE clause:
    Code:
    CRITL_BAT.PROC_NM_TXT = PROD_ABEND.JOB_TXT
    (I swapped the order to make the comparison easier)

    If the PROC_NME_TXT and the JOB_TXT have to match exactly, don't use the SUBSTR in the ON Clause (then the line in the WHERE clause would not be needed (and the function is probably interfering with index usage).
    Code:
     FROM                                                        
       TOTDB01.CRITL_BAT CRITL_BAT 
         INNER JOIN                    
       TOTDB01.PROD_ABEND PROD_ABEND                           
         ON CRITL_BAT.PROC_NM_TXT = PROD_ABEND.JOB_TXT
     WHERE                                                       
         CRITL_BAT.SCHDG_SYS_TXT = 'ZEKE';      
    
    or
    
     FROM                                                        
       TOTDB01.CRITL_BAT CRITL_BAT 
         INNER JOIN                    
       TOTDB01.PROD_ABEND PROD_ABEND                           
         ON     CRITL_BAT.PROC_NM_TXT   = PROD_ABEND.JOB_TXT
            AND CRITL_BAT.SCHDG_SYS_TXT = 'ZEKE';
    Also, since the only column used from CRITL_BAT is this same column, you could replace it in Select clause with the column from PROD_ABEND. Then you could eliminate the join and do an WHERE EXISTS subquery. It is at least another option to try.

Posting Permissions

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