Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2007
    Posts
    1

    Unanswered: DB2 Query Anlysis

    Hi,

    The query mention below is taking long time to execute can any body help me
    out in perfromance tuning of the query

    exec sql
    Declare Cursor CUR1
    Select A.C1, A.C2,A.C3,A.C4
    from Tbl1 A, Tbl2 B, Tbl3 C
    Where
    -- Join Predicates
    ( A.C1 = B.C1 AND
    A.C2 = B.C2 AND
    A.C1 = C.C1 AND
    A.C2 = C.C2 )
    --end predicates
    AND (C.Region = CLGEN-REG)
    AND (B.Date <> '00000000' )
    AND
    ((B.Date < clgen-date)
    or
    (B.Date >= clgen-date
    A.IND = 'Y'))
    order by A.C1,A.C2,A.C3,A.C4
    group by A.C1,A.C2,A.C3,A.C4
    end-exec

    All the tables have are accessed by the keys C1 and C2
    and the table tbl1 is indexed by C1,C2,C3,C4

    the Columns A.IND,C.Region and B.Date are not indexed and the DB2 plan is performing Table scan the Db2 table

    Is that right to index this coulmns A.IND and Trig_dt.
    A.IND will have only two value 'y' or 'N'.

    How can I make my query give resultant data faster

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    What version and platform is DB2 in your environment?


    Have you done RUNSTATS on the table - preferably with distribution statistics?

    Do you really need the order by clause ? group by doesn't seem to be used.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    It's difficult to tune SQL, when all you have is the SQL. What do you mean by a taking a long time? What would you consider an acceptable run time? How many rows are on each table? How current are the stats? What's the cardinality for C.Region, B.Date, and A.Ind? What are the existing indexes? Just guessing, I'd suggest considering an index on C.Region, B.Date, and maybe, just maybe, A.Ind, but the best anyone could do would be a guess.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by praveenbl22
    Hi,

    The query mention below is taking long time to execute can any body help me
    out in perfromance tuning of the query
    Code:
    exec sql
    Declare Cursor CUR1
      Select A.C1, A.C2,A.C3,A.C4
      from Tbl1 A, Tbl2 B, Tbl3 C
      Where 
    -- Join Predicates
         ( A.C1  = B.C1  AND 
           A.C2  = B.C2  AND
           A.C1  = C.C1  AND 
           A.C2  = C.C2  )
    --end predicates
         AND (C.Region = :DCLGEN-REG)
         AND (B.Date <> '00000000' )
                AND
                 ((B.Date < :Dclgen-date)
                   or    
                   (B.Date  >= :Dclgen-date
                     A.IND = 'Y'))
     order by A.C1,A.C2,A.C3,A.C4
     group by A.C1,A.C2,A.C3,A.C4
    end-exec
    What's the point of the GROUP BY clause? You don't do any aggregation, so this clause seems to be irrelevant to me.

    the Columns A.IND,C.Region and B.Date are not indexed and the DB2 plan is performing Table scan the Db2 table

    Is that right to index this coulmns A.IND and Trig_dt.
    A.IND will have only two value 'y' or 'N'.
    What do you expect? No index means that you don't have fast access to qualifying rows. I would add indexes on the three columns and see how that works out. Maybe it doesn't deliver much benefits indexing A.IND.

    Also, you seem to have a broken design if you check for something like "B.DATE <> '00000000'". Couldn't you use SQL NULL to represent non-existing values and also use the DATE data type?

    Something else to consider is that "(col < :val or (col >= :val and ...))" is difficult for DB2 to evaluate using an index. You could consider how the query behaves if you use a query with a UNION ALL instead of the OR predicate.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by stolze
    What's the point of the GROUP BY clause? You don't do any aggregation, so this clause seems to be irrelevant to me.
    It would be nice to see explain. I thing DB2 access plan engine is so smart to eliminate "group by" in this sample so this should not be the case of performance problem.

    @praveenbl22: I would like to see explain, table definitions, index definitions, primary key definitions and foreign key definitions.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by grofaty
    I thing DB2 access plan engine is so smart to eliminate "group by" in this sample so this should not be the case of performance problem.
    Yes, you are correct, of course. My question was more of a hint to revisit the SQL statement - because either the statement is not the true one to be executed, contains mistakes, or the one who wrote it has some misconceptions on SQL.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by stolze
    My question was more of a hint to revisit the SQL statement - because either the statement is not the true one to be executed, contains mistakes, or the one who wrote it has some misconceptions on SQL.
    I agree. First think of solving SQL performance problem is understanding what it is doing. If it makes some redundant tasks, then rewrite SQL may help.

    According to my experiences at least 50% times there is badly written SQL that causes performance problems. Then at 40% there is missing index and in 10% there is bad data model or doing the things that are just taking a lot of CPU power.

Posting Permissions

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