Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2017
    Posts
    1

    Unanswered: SQL help needed in removing sub-query

    I have a poorly performing SQL that contains a sub-query that is not co-related. I replaced it with a co-related sub-query but performance got worse. Is there a way to replace the sub-query with a join?

    Below is a simplified version of the query.

    Code:
    select distinct tab1.app_id, 
    	tab1.name, 
            tab1.stat_cd, 
            tab1.qr 
    from apps tab1 , issues tab2 
    where 
    	tab1.app_id = tab2.app_id and 
            tab1.qr = 'm' and 
            tab2.iqr = 'm' and 
    	tab1.app_id not in 
            ( 
    		select  distinct tab3.app_id 
    		from issues tab3 
    		where tab3.iqr = 'm' and 
    		( 
                       tab3.i_cd = 'f' or 
                       tab3.i_cd = 'r' or 
                       tab3.i_cd = 'c' 
               	) 
            )
    Any tip or suggestion is appreciated. Thank you.

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    577
    Provided Answers: 3
    Look at the explain plan and see what is happening.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    May 2009
    Posts
    509
    Provided Answers: 1
    Without knowing anything about indexes or cardinality, you might try one of these:

    Code:
    select distinct tab1.app_id, 
                    tab1.name, 
                    tab1.stat_cd, 
                    tab1.qr 
    from apps   tab1
       , issues tab2 
    where tab1.app_id = tab2.app_id
      and tab1.qr     = 'm'
      and tab2.iqr    = 'm'
      and tab2.i_cd not in ('f','r','c')
    Code:
    select distinct tab1.app_id, 
                    tab1.name, 
                    tab1.stat_cd, 
                    tab1.qr 
    from apps tab1
    where tab1.qr = 'm'
      and exists (Select *
                  from issues tab2 
                  where tab1.app_id = tab2.app_id
                    and tab2.iqr    = 'm'
                    and tab2.i_cd not in ('f','r','c')
                 )

Tags for this Thread

Posting Permissions

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