Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    3

    Unanswered: To optimize the query

    Hi All,

    I am new to DB2.

    The following code gives the correct solution.But the performance of the query is good when the table contains less no of rows and it is bad when the no of rows in the table is large.

    I need the optimization of the following query, so that it will take minimum CPU usage and time.

    SELECT A.ACCTNO, T.QTY
    FROM ACC A LEFT OUTER JOIN TRD T
    ON A.ACCTNO = T.ACCTNO
    WHERE T.QTY > 250000

    Thanks in advance for your replies.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What is the DDL for the two tables? Also include any indexes for the tables. Please provide DB2 version and OS.

    Andy

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    your query will not work ( regardless of any optimization ) !

    SELECT A.ACCTNO, T.QTY
    FROM ACC A LEFT OUTER JOIN TRD T
    ON A.ACCTNO = T.ACCTNO
    WHERE T.QTY > 250000

    you are using a left outer join.
    If a row of table A has no matching columns in table T all values of T will be <NULL>
    none of these row will satisfy the WHERE-clause T.QTY > 250000 as <NULL> is never greater than any value

    --> DB2 wil rewrite your querry to an INNER JOIN

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    So, assuming that your query returned the correct result, i.e., you didn't need the outer join (as umayer pointed out correctly), the following will certainly be more performant and equivalent:
    Code:
    SELECT A.ACCTNO, T.QTY
    FROM   ACC as A INNER JOIN TRD as T ON A.ACCTNO = T.ACCTNO
    WHERE  T.QTY > 250000
    Also try the following version, which might perform better (but probably it will be exactly the same):
    Code:
    SELECT A.ACCTNO, T.QTY
    FROM   (SELECT ACCTNO FROM ACC) as A
           INNER JOIN
           (SELECT ACCTNO, QTY FROM TRD WHERE QTY > 250000) as T
           ON A.ACCTNO = T.ACCTNO
    And of course add indexes on TRD.QTY, TRD.ACCTNO, ACC.ACCTNO and the combined (TRD.QTY,TRD.ACCTNO) to speed up the query...
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Jan 2007
    Location
    China
    Posts
    5
    Umayer's analysis is good.

Posting Permissions

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