Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2010
    Posts
    4

    Unanswered: Query Optimization

    SELECT
    T1.CT_ID,
    T1.LT_UPD,
    T1.CREATED,
    T1.LT_UPD_BY,
    T1.CREATED_BY,
    T1.M_NUM,
    T1.ROW_ID,
    T1.CURCY_CD,
    T1.X_ATTRIB_15
    FROM
    SIE.C_CLAIM_M_M T1
    INNER JOIN SIE.S_IN_CLAIM_M T2 ON T1.PAR_ROW_ID = T2.ROW_ID
    WHERE
    ((T1.TYPE = 'Transaction Reserve') AND
    (T2.ATTRIB_03 = 'CLAIM')) AND
    (T1.CLAIM_ID = '1-CG9X0S')
    ORDER BY
    T1.CLAIM_ID, T1.X_ATTRIB_18

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Is it not homework?

    Some ideas to rewrite the query are as followings....
    Although they may influence performance or not, they may improve readability and maintenability.

    1) "INNER JOIN SIE.S_IN_CLAIM_M T2" can be replaced by EXISTS, if T2.ROW_ID is unique.
    Because, no column of T2 was included in the select list.

    2) T1.CLAIM_ID is not neccesary in ORDER BY clause.
    Because T1.CLAIM_ID = '1-CG9X0S' was specified in WHERE clause.

    3) I couldn't find any reason to use unnecessary parentheses in WHERE conditions.
    They harm readability, and may be cause of syntax error at the time of maintenance.

  3. #3
    Join Date
    Aug 2010
    Posts
    4
    It was working earlier and stuck in between....can any one suggest the best way to optimize this sql query.

  4. #4
    Join Date
    Aug 2010
    Posts
    4
    This query was working for the last 2 months. can any one suggest what would be the best way to do the performance tuning for the above listed query. the way to optimize the problem.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I, believe, Tonkuma has already answered you on quite a few points that would help speed this query up. If you take his suggestions on the order by and exists subselect, you should see improvement. The other advice for readability and maintenance is, also, spot on.
    Dave

  6. #6
    Join Date
    Aug 2010
    Posts
    4
    Its system generated query as I told earlier for the last 2 months it was working properly and giving prompt results but this time i am facing the problem of long-running query. kindly suggest ....both tables are properly indexed.

  7. #7
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    - reorg and runstats tables used in the query;
    - do not assume that your "system" created a perfect query. listen to suggestion given here;
    - do not assume that existing indexes are actually used just because you think they are used.
    index usage should bechecked by proper db2 tools.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

Posting Permissions

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