Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2008
    Posts
    3

    Question Unanswered: Which SQL is better on perfomance point of view in DB2 & how conditional where clause

    Hi All,

    I have two table. table details are

    table one(Cust_det) has two fields (Cust_cin and cust_name).
    table two(cust_add_det) has cust_cin and cust_address fields.

    To join above two table which sql is better on perfomance point of view?
    and how conditional where clause works?

    select A.cust_cin,B.cust_address from Cust_det A,cust_add_det B where
    A.Cust_cin = ? and A.cust_cin=B.cust_cin

    or

    select A.cust_cin,B.cust_address from Cust_det A,cust_add_det B where
    A.cust_cin=B.cust_cin and A.Cust_cin = ?

  2. #2
    Join Date
    Jan 2008
    Posts
    3

    Question which sql is better on perfomance in db2 and how conditional where clause works?

    Hi All,

    I have two table in DB2 (ver 8). table details are

    table one(Cust_det) has two fields (Cust_cin and cust_name).
    table two(cust_add_det) has cust_cin and cust_address fields.

    To join above two table which sql is better on perfomance point of view?
    and how conditional where clause works?

    select A.cust_cin,B.cust_address from Cust_det A,cust_add_det B where
    A.Cust_cin = ? and A.cust_cin=B.cust_cin

    or

    select A.cust_cin,B.cust_address from Cust_det A,cust_add_det B where
    A.cust_cin=B.cust_cin and A.Cust_cin = ?

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It makes no difference in what order your list the predicates in the WHERE clause. DB2 will figure out which predicate to evalute first (whether to do the join first, or filter the rows first).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jan 2008
    Posts
    3
    Thanks for your rely, Am using db2 version 8. whether DB2 will figure out which predicate to evalute first in version 8?. it seems db2 wont support this future in verision 8.

    Also how conditional where clause works? in case absence of figure out which predicate to evalute first by db2?

    As a part of Performance tuning we are changing the where clause order. it showing some difference in time(exec time) after modified.. i think db2 ver 8 wont support this? Please correct me if am wrong.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You are wrong. No matter what version you have, DB2 will decide the order of predicate evaluation, regardless of the order or the predicates in the WHERE clause. The only exception might be if you changed the query optimization level (default is 5) but even then I doubt it would make a difference.

    I don't know what you mean by conditional WHERE clause. I suspect the SQL statement you posted is not the real one, which may account for the problem.

    The primary factors that affect the decision that DB2 makes about which predicate to evaluate first are:

    1. Physical condition of the rows and indexes with regard to clustering, freespace, disorganization, etc. This can be changed with a reorg command.

    2. The statistics that DB2 has regarding the number of rows and the items contained in number 1 above. This can can be updated with runstats command.

    Also, if you have any outer joins, ORDER BY, GROUP BY, etc, these could affect predicate order evaluation.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by damodharan
    select A.cust_cin,B.cust_address from Cust_det A, cust_add_det B
    where A.Cust_cin = ? and A.cust_cin=B.cust_cin

    or

    select A.cust_cin,B.cust_address from Cust_det A,cust_add_det B
    where A.cust_cin=B.cust_cin and A.Cust_cin = ?
    Actually, DB2 will even apply what is called "transitive closure", by adding the condition
    Code:
    B.cust_cin = ?
    so the above query is essentially rewritten into
    Code:
    SELECT A.cust_cin,B.cust_address
    FROM   (SELECT cust_cin
            FROM   cust_det
            WHERE  cust_cin = ?) A,
           (SELECT cust_address
            FROM   cust_add_det
            WHERE  cust_cin = ?) B
    WHERE  A.cust_cin=B.cust_cin
    which is completely symmetric in tables A and B.
    Of course, DB2 will decide on the join method and the two table accesses, based on presence of indexes and on statistics (like size of tables), hence the implementation will most often not be symmetric in the two tables.

    If you are having performance issues, they will rather be solved by database changes (like adding indexes or running RUNSTATS) rather than rewriting the query.
    Unless of course your query contains other ingredients than in the query above, like e.g. scalar functions in the WHERE conditions, in which case a query rewrite might be very necessary...
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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