Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: can we use Exists instead of IN

    Hi;

    In below query(Example:1) is taking more time to return the result set

    Both table has more number of records

    Example :1
    Code:
    select
    
    t1.SE_ASS
    ,t1.RE_ASS
    ,t1.UF_ASS 
    ,t2.IN_DT
    from TABLE1 T1
    inner join
    
             TABLE2 T2
    ON                                              
        T1.LAN_TO          = T2.LAN_TO      
    AND t1.SE_ASS = t2.SE_ASS  
    AND t1.RE_ASS = t2.RE_ASS
    AND T1.UF_ASS = t2.UF_ASS  
    
    where 
    
     t1.LAN_TO='AAA' 
    AND  t1.TEPL_IND  IN ('A',' ')
    so for testing purpose i was trying to remove the below condition
    Code:
    AND  t1.TEPL_IND  IN ('A',' ')
    and used the below query instead of that
    Code:
    AND (EXISTS (                                             
       SELECT 1                                               
        FROM     TABLE1 A                            
       WHERE     A.LAN_TO  = T1.LAN_TO        
         AND     A.SE_ASS  = t1.SE_ASS
         AND     A.RE_ASS  = t1.RE_ASS 
         AND     A.UF_ASS  = T1.UF_ASS
         AND     A.TEPL_IND   = 'A'                   
                  )                                           
    OR  EXISTS (                                              
        SELECT 1                                              
        FROM     TABLE1 B                            
      WHERE     B.LAN_TO   = T1.LAN_TO        
         AND     B.SE_ASS  = t1.SE_ASS
         AND     B.RE_ASS  = t1.RE_ASS 
         AND     B.UF_ASS  = T1.UF_ASS
         AND     B.TEPL_IND   = ' '                
                   )                                          
       )
    Below is the complete query after the changes and it ran fastly
    Example :2
    Code:
    select
    
    t1.SE_ASS
    ,t1.RE_ASS
    ,t1_UF_ASS 
    ,t2.IN_DT
    from TABLE1 T1
    inner join
    
             TABLE2 T2
    ON                                              
        T1.LAN_TO          = T2.LAN_TO      
    AND t1.SE_ASS = t2.SE_ASS  
    AND t1.RE_ASS = t2.RE_ASS
    AND T1.UF_ASS = t2_UF_ASS  
    
    where 
    
     t1.LAN_TO='AAA' 
    AND (EXISTS (                                             
       SELECT 1                                               
        FROM     TABLE1 A                            
       WHERE     A.LAN_TO  = T1.LAN_TO        
         AND     A.SE_ASS  = t1.SE_ASS
         AND     A.RE_ASS  = t1.RE_ASS 
         AND     A.UF_ASS  = T1.UF_ASS
         AND     A.TEPL_IND   = 'A'                   
                  )                                           
    OR  EXISTS (                                              
        SELECT 1                                              
        FROM     TABLE1 B                            
      WHERE     B.LAN_TO   = T1.LAN_TO        
         AND     B.SE_ASS  = t1.SE_ASS
         AND     B.RE_ASS  = t1.RE_ASS 
         AND     B.UF_ASS  = T1.UF_ASS
         AND     B.TEPL_IND   = ' '                
                   )                                          
       )
    But the returned results are different,Example-2 gives the unmatching TEPL_IND column values also

    ,means that we need to fetch records based on the condition like ..t1.TEPL_IND IN ('A',' ')

    Please help me on that the Example-2 query is correct ?

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    If you prefer "EXISTS", try this:
    Code:
    select SE_ASS, RE_ASS, UF_ASS,t2.IN_DT
    from TABLE2 t2
    WHERE EXISTS
     (SELECT 1 FROM TABLE1
      WHERE LAN_TO = t2.LAN_TO      
          AND SE_ASS = t2.SE_ASS  
          AND RE_ASS = t2.RE_ASS
          AND UF_ASS = t2.UF_ASS  
          AND LAN_TO='AAA' 
          AND TEPL_IND  IN ('A',' '))
    I don't expect the "OR" to be more performant than the "IN", to the contrary.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think rewriting "t1.TEPL_IND IN ('A',' ')" by EXISTS is useless, in performance and to simplify/clarify the query.

    taking more time to return the result set
    Please take EXPLAIN and see where is the cause of poor performance, before atempt to rewite the query.
    If table scan was in TABLE1 or TABLE2, try to add indexes.
    Although you didn't wrote your DB2 version/release and platform OS,
    if you are using DB2 for LUW, db2advis might help you.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example-2 gives the unmatching TEPL_IND column values also
    If you couldn't understand the reason of extra rows returned,
    my advice is to see the value of t1.TEPL_IND and the results of two EXISTS predicates, like...
    Code:
    select
    
      t1.SE_ASS
    , t1.RE_ASS
    , t1_UF_ASS 
    , t2.IN_DT
    , t1.TEPL_IND
    , CASE
      WHEN EXISTS
          (SELECT 1
            FROM  TABLE1 A
            WHERE A.LAN_TO   = T1.LAN_TO
              AND A.SE_ASS   = t1.SE_ASS
              AND A.RE_ASS   = t1.RE_ASS
              AND A.UF_ASS   = T1.UF_ASS
              AND A.TEPL_IND = 'A'
          ) THEN
           'Exists TEPL_IND = A'
      ELSE '**No TEPL_IND = A**'
      END  exists_1
    , CASE
      WHEN EXISTS
          (SELECT 1
            FROM  TABLE1 B
            WHERE B.LAN_TO   = T1.LAN_TO
              AND B.SE_ASS   = t1.SE_ASS
              AND B.RE_ASS   = t1.RE_ASS
              AND B.UF_ASS   = T1.UF_ASS
              AND B.TEPL_IND = ' '
          ) THEN
           'Exists TEPL_IND = blank'
      ELSE '**No TEPL_IND = blank**'
      END  exists_2
    from TABLE1 T1
    inner join
    
             TABLE2 T2
    ON                                              
        T1.LAN_TO          = T2.LAN_TO      
    AND t1.SE_ASS = t2.SE_ASS  
    AND t1.RE_ASS = t2.RE_ASS
    AND T1.UF_ASS = t2_UF_ASS  
    
    where 
    
     t1.LAN_TO='AAA' 
    AND (EXISTS (                                             
       SELECT 1                                               
        FROM     TABLE1 A                            
       WHERE     A.LAN_TO  = T1.LAN_TO        
         AND     A.SE_ASS  = t1.SE_ASS
         AND     A.RE_ASS  = t1.RE_ASS 
         AND     A.UF_ASS  = T1.UF_ASS
         AND     A.TEPL_IND   = 'A'                   
                  )                                           
    OR  EXISTS (                                              
        SELECT 1                                              
        FROM     TABLE1 B                            
      WHERE     B.LAN_TO   = T1.LAN_TO        
         AND     B.SE_ASS  = t1.SE_ASS
         AND     B.RE_ASS  = t1.RE_ASS 
         AND     B.UF_ASS  = T1.UF_ASS
         AND     B.TEPL_IND   = ' '                
                   )                                          
       )
    ;

  5. #5
    Join Date
    Nov 2011
    Posts
    334
    maybe the following sql will remove the extra unmachting row
    Code:
    select
    
    t1.SE_ASS
    ,t1.RE_ASS
    ,t1_UF_ASS 
    ,t2.IN_DT
    from TABLE1 T1
    inner join
    
             TABLE2 T2
    ON                                              
        T1.LAN_TO          = T2.LAN_TO      
    AND t1.SE_ASS = t2.SE_ASS  
    AND t1.RE_ASS = t2.RE_ASS
    AND T1.UF_ASS = t2_UF_ASS  
    
    where 
    
     t1.LAN_TO='AAA' 
    AND (EXISTS (                                             
       SELECT 1                                               
        FROM     TABLE1 A                            
       WHERE     A.LAN_TO  = T1.LAN_TO        
         AND     A.SE_ASS  = t1.SE_ASS
         AND     A.RE_ASS  = t1.RE_ASS 
         AND     A.UF_ASS  = T1.UF_ASS
         AND     T1.TEPL_IND   = 'A'                   
                  )                                           
    OR  EXISTS (                                              
        SELECT 1                                              
        FROM     TABLE1 B                            
      WHERE     B.LAN_TO   = T1.LAN_TO        
         AND     B.SE_ASS  = t1.SE_ASS
         AND     B.RE_ASS  = t1.RE_ASS 
         AND     B.UF_ASS  = T1.UF_ASS
         AND     T1.TEPL_IND   = ' '                
                   )                                          
       )

Posting Permissions

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