Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2007
    Posts
    30

    Unanswered: query performance

    i have the following query running which was running a bit slow.(it was not written by me)can any one suggest me how i could improve the performance.its not doing any table scans except inthe first CTE.even though there is a index on the join column its doing table scan as there are no predicates except the flag.how can i rewrite this query with out CTE's.
    db2 V8.2fp5 solaris environment
    with online AS.
    (SELECT exc.snb,
    exc.dets,
    EC.ccx,
    EC.cls_NAME_TX,
    EC.SHORT_DESCRIPTION_TX,
    exc.SCORE_NB,
    exc.sts_cd
    FROM db2udb.online_XUSER_cls exc,
    db2udb.online_cls EC
    WHERE exc.cls_UUID_TX = E.PK_cls_UUID_TX
    AND exc.cls = 'N'),
    DATES AS
    (SELECT snb,
    ccx,
    COUNT ( ccx ) AS TIMES_TAKEN,
    MIN ( dets ) AS MinDATE,
    MAX ( dets ) AS MaxDATE
    FROM online
    WHERE sts_cd <> 'E'
    GROUP BY snb,
    ccx)
    SELECT EM.EMPLOYEE_WORK_LOCATION,EM.snb,EM.EMPLOYEE_NB,EM .FIRST_NM,
    EM.LAST_NM,ET.dets,EM.sts_cd AS DEFAULT_sts_cd,ONL.ccx,
    ONL.cls_NAME_TX,ONL.SHORT_DESCRIPTION_TX,ONL.SCORE _NB,
    ONL.sts_cd,DT.TIMES_TAKEN,DT.MinDATE,DT.MaxDATE
    FROM EMPLOYEES EM
    LEFT JOIN online ONL ON ONL.snb = EM.snb AND ONL.ccx = ES.ccx
    LEFT JOIN DATES DT ON DT.snb = EM.snb AND DT.ccx = ONL.ccx
    ORDER BY ONL.sts_cd,
    EM.FIRST_NM
    any help is appreciated thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Before you try to rewrite it, you need to figure out why it is doing a table scan. How many rows are in db2udb.online_XUSER_cls? How many of those have cls = 'N'? Is there an index on cls?

    Andy

  3. #3
    Join Date
    Dec 2007
    Posts
    30
    the table is 1.5 million and cls is also almost the same and there is no
    index on cls since its a flag i thoght there would not be much use.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You are better off not doing anything. Since you say that cls = 'N' is almost the entire table, than doing a table scan is not a bad plan to retrieve 98% of the table.

    Andy

Posting Permissions

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