Help speed up query?

goldfishhh · #1 (**permalink**) 06-04-10, 12:01

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;

Stealth_DBA · #2 (**permalink**) 06-04-10, 12:38

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech