Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2011
    Posts
    1

    Unanswered: Performance Tuning for the DB2 query

    Hi All,

    We are experiencing high execution times some times on the following query.

    SELECT COUNT(A.KEY),
    B.DT,
    HOUR(B.TM),
    C.ID,
    D.APPL
    FROM CLIENT A,
    DETAIL B,
    EMPLOYEE C,
    MAINTENANCE D
    WHERE A.ID = :L-ID
    AND A.KEY_NBR = B.KEY_NBR
    AND B.DT >= DATE(:L-START-DT)
    AND B.DT <= DATE(:L-END-DT)
    AND A.KEY_NBR = C.KEY_NBR
    AND A.KEY_NBR = D.KEY_NBR
    GROUP BY B.DT, HOUR(B.TM),
    C.ID, D.APPL

    All the tables used in this Query have Multi Million Records, Is there a way that i can reduce the execution time of this Query.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

  3. #3
    Join Date
    Nov 2011
    Posts
    334
    if you could, Plz post the access plan for the query ( db2exfmt output ) here.
    also you can run db2advis on it to see whether there are some indexes you can create to improve query performance.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    As others have mentioned, you have not supplied enough information. However, I would change the following:

    1. SELECT COUNT(A.KEY) change to SELECT COUNT(*)

    2. AND B.DT >= DATE(:L-START-DT) AND B.DT <= DATE(:L-END-DT)
    change to AND B.DT BETWEEN :L-START-DT and :L-END-DT
    (make sure the host variables are in char format of 'YYYY-MM-DD' in your program. No need to use DATE Function.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Marcus_A View Post

    1. SELECT COUNT(A.KEY) change to SELECT COUNT(*)
    This definitely depends on what they want to count - the number of rows or the number of A.KEY values that are not null.

    Quote Originally Posted by Marcus_A View Post
    2. AND B.DT >= DATE(:L-START-DT) AND B.DT <= DATE(:L-END-DT)
    change to AND B.DT BETWEEN :L-START-DT and :L-END-DT
    Actually, in many cases the optimizer will rewrite "B.DT BETWEEN :L-START-DT and :L-END-DT" back to "B.DT >= DATE(:L-START-DT) AND B.DT <= DATE(:L-END-DT)".

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by n_i View Post
    This definitely depends on what they want to count - the number of rows or the number of A.KEY values that are not null.
    I am assuming that A.KEY is the PK and is defined as NOT NULL.

    Quote Originally Posted by n_i View Post
    Actually, in many cases the optimizer will rewrite "B.DT BETWEEN :L-START-DT and :L-END-DT" back to "B.DT >= DATE(:L-START-DT) AND B.DT <= DATE(:L-END-DT)".
    That may be, but I am also assuming because of host variables used in the SQL (with the colon preceeding the host variable in the SQL) that this is DB2 for z/OS (It could be DB2 LUW with embeded SQL in a language like C, but that is not very common these days). IBM may have changed it, but at one time I know for sure that when using host variables on DB2 for z/OS that the BETWEEN syntax has a higher filter factor than >= and <=, and is therefore more likely to use an index. Higher filter factor means that DB2 assumes few rows will be returned. It is certainly possible that this has been changed on DB2 for z/OS to convert it for you like on LUW.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Nov 2011
    Posts
    24
    Quote Originally Posted by Marcus_A View Post
    As others have mentioned, you have not supplied enough information. However, I would change the following:
    1. SELECT COUNT(A.KEY) change to SELECT COUNT(*)
    [QUOTE=Marcus_A;6527901]I am assuming that A.KEY is the PK and is defined as NOT NULL.

    QUOTE]


    Iam not a expert, but generally SELECT COUNT(*) is said to cost more than
    SELECT COUNT(column1)...??? (This applies in MS SQL Server sure)
    In this particular case, how does it matter A.KEY or any other column is used in the count() as it merely counts the row? Select COUNT(1) would also do the job?

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by techday View Post
    Iam not a expert, but generally SELECT COUNT(*) is said to cost more than
    SELECT COUNT(column1)...??? (This applies in MS SQL Server sure)
    In this particular case, how does it matter A.KEY or any other column is used in the count() as it merely counts the row? Select COUNT(1) would also do the job?
    In some cases (not sure about this case) using count(*) is more efficient than count(column-name) since the query may use an index access where that column does not exist, and then DB2 also has to read the column off the table to do the counting.

    In DB2, count(*) does not cost more than count(A.KEY) if A.KEY is a column on the table. In some cases they will be the same, but count(A.KEY) will not be faster.

    As to SQL Server, I have no idea, but I would seem very strange to me that count(*) would be slower in any DBMS.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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