Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2008
    Posts
    48

    Unanswered: Query Performance With IN and Subquery

    i am running query like below

    select field1 from Tab1 where (tab1.field2,tab1.field3,tab1.field4) in
    (select tab2.field2,tab2.field3,tab2.field4 from Tab2 where
    (tab2.field5,tab2.field6) in (select tab3.field5,tab3.field6 from tab3 where tab3.field7 = 'somevalue')
    )

    it takes long time to run. i have proper indexes.
    Even when subquery
    select tab3.field5,tab4.field6 from tab4 where tab4.field7 = 'somevalue'
    return 0 records , it is slow

    i changed query to use exists

    select field1 from Tab1 where exists (select 1 from tab2
    where (tab1.field2,tab1.field3,tab1.field4) = (tab2.field2,tab2.field3,tab2.field4 ) and exists (select 1 from tab3 where (tab2.field5,tab2.field6)
    = (tab3.field5,tab3.field6 ) and tab3.field7 = 'somevalue' )
    )

    i tried

    select field1 from Tab1 , tab2, Tab3 where
    tab3.field7 = 'somevalue'
    and (tab1.field2,tab1.field3,tab1.field4) = (tab2.field2,tab2.field3,tab2.field4 )
    and (tab2.field5,tab2.field6) = (tab3.field5,tab3.field6 )

    i also used with comand

    with Tab3a as (select tab3.field5,tab3.field6 from Tab3 where tab3.field7 = 'somevalue' ),
    Tab2a as (select tab2.field2,tab2.field3,tab2.field4 from Tab2 where
    (tab2.field5,tab2.field6) in (select tab3a.field5,tab3a.field6 from tab3a) )
    select field1 from Tab1 where (tab1.field2,tab1.field3,tab1.field4) in
    (select tab2a.field2,tab2a.field3,tab2a.field4 from Tab2a )


    tab1 has 501918 records
    tab2 has 5806749
    To keep example simple i have used ony 2 subqueries.
    My actual query has one more subquery and some of the inner queries have multiple joins

    I am using DB2 v9.5. Thanks in advance

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Why not write it as a simple join?

    Code:
    select t1.field1 
    from tab3 as t3
    inner join tab2 as t2 on (t2.field5 = t3.filed5 and t2.field6 = t3.field6)
    inner join tab1 as t1 on (t1.field2 = t2.field2 and t1.field3 = t2.field3 and t1.field4 = t2.field4)
    where t3.field7 = 'somevalue'
    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by phil72 View Post
    i have proper indexes.
    How do you know this?

  4. #4
    Join Date
    Nov 2008
    Posts
    48
    i tried simple join too
    i tried

    select field1 from Tab1 , tab2, Tab3 where
    tab3.field7 = 'somevalue'
    and (tab1.field2,tab1.field3,tab1.field4) = (tab2.field2,tab2.field3,tab2.field4 )
    and (tab2.field5,tab2.field6) = (tab3.field5,tab3.field6 )

    I ran the query advisor and i have already created Indexes

    Query run fast if subquery returns results, but if subquery returns 0 records , whole query takes long time to return 0 records
    Last edited by phil72; 06-06-11 at 13:01.

Posting Permissions

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