Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2003
    Posts
    9

    Unanswered: how to improve this query performance

    hi,

    Can u suggest a way to improve this query performance?
    I have 2 tables T1,T2.
    T1 has two numeric fields n1,n2 which together form the primary key.
    T2 also has the fields n1,n2 which together reference the values of T1.

    query:

    select T1.n1,T1.n2
    from T1,T2
    where
    char(T1.n1)||char(T1.n2) not in char(T2.n1)||char(T2.n2)

    My problem is the this query takes a long time to complete as T1 has
    over 5000 rows.


    PS: I was told that there is a feature in db2 by which i can say
    select (n1,n2) from T1,T2
    where
    (T1.n1,T1.n2) not in (T2.n1,T2.n2).
    Can someone throw some light on that?

    Thanks,
    K Rajkumar

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am not sure what your are trying to do, but it sounds like you want the rows in table1 which don’t exist in table2.

    Select A.n1, A.n2
    from T1 A
    where not exists
    (select B.n1 from T2 B
    where A.n1 = B.n1 and A.n2 = B.n2)

    Make sure T2 has a composite index on (n1, n2). There can be other columns in the index if needed, but n1 and n2 should be the first 2 columns of the index for best performance.

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Try to write subselect like this:
    select n1, n2 from T1 where (n1,n2) not in (select n1, n2 from T2)

    I also suggest you to go through the How to improve the performance? thread already discussed in this forum.

    Hope this helps,
    Grofaty

  4. #4
    Join Date
    Sep 2003
    Posts
    9
    Grofaty,

    I tried your query.But it is giving me a syntax error.
    My db2 is Version: V5R1M0 010525
    that runs on a AS/400 system.
    Could that be a problem?

    Reg,
    K Rajkumar

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I think that my query and grofaty's is the same. But as you noted, his syntax may not be supported by your verison. They both do the same thing and should perform the same.

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    That was an interesting event! When I started to write the reply there was no replys, but when I sumited it then the Marcus_A had already posted the message. So the answers are almost the same... Funny!

    As Marcus_A said, your database version does not suppot the above SQLs.

    There is one more tip (I don't know if this is supported with your version of db2):

    select n1, n2 from T1
    except
    select n1, n2 from T2

    If this works, consider the Marcus_A tip about indexes.

    Hope this helps,
    Grofaty

  7. #7
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230
    Originally posted by Marcus_A
    I am not sure what your are trying to do, but it sounds like you want the rows in table1 which don’t exist in table2.

    Select A.n1, A.n2
    from T1 A
    where not exists
    (select B.n1 from T2 B
    where A.n1 = B.n1 and A.n2 = B.n2)

    Make sure T2 has a composite index on (n1, n2). There can be other columns in the index if needed, but n1 and n2 should be the first 2 columns of the index for best performance.
    It is better instead of (select B.n1) using select 1 and do not make trouble for db2 for searching B.n1 , because the result of not exist is true or false.
    Thanks

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Maybe or maybe not.

    DB2 has to find the column B.n1 anyway because it is part of the WHERE clause. The value is not returned to the application program because it is part of the EXISTS clause and is not included in the columns retrieved to the program in the main SELECT.

    I don't think DB2 will be bothered by such an inclusion of the column in the SELECT clause of the sub-select. But that is just my opinion.

  9. #9
    Join Date
    Oct 2003
    Location
    York UK
    Posts
    9
    query can further be improved by

    Select A.n1, A.n2
    from T1 A
    where not exists
    (select 1 from T2 B
    where A.n1 = B.n1 and A.n2 = B.n2)



    Originally posted by Marcus_A
    I am not sure what your are trying to do, but it sounds like you want the rows in table1 which don’t exist in table2.

    Select A.n1, A.n2
    from T1 A
    where not exists
    (select B.n1 from T2 B
    where A.n1 = B.n1 and A.n2 = B.n2)

    Make sure T2 has a composite index on (n1, n2). There can be other columns in the index if needed, but n1 and n2 should be the first 2 columns of the index for best performance.

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    That's what M_RAS said. But I am not so sure, for the reasons that I already gave.

  11. #11
    Join Date
    May 2003
    Location
    San Juan, PR
    Posts
    18
    Try this statement:

    Select A.n1, A.n2
    from T1 A left outer join T2 B
    on A.n1 = B.n1 and A.n2 = B.n2
    where B.n1 is null

Posting Permissions

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