Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2007
    Posts
    27

    Unanswered: SQL query tuning in db2

    hi
    i am new to db2 and SQL. i need to tune the following query for improving the performance.its currently taking an hour to excute the query.what possible changes could be made to improve the performance of this query.
    please help me ....any help would be greatly appreciated.

    with t_t1 (TMP_TRAINER_EMPLOYEE_NB) as (
    select TRAINER_EMPLOYEE_NB from wer.trainer_scorecard
    where SAFETY_RANKING_QY BETWEEN .001 AND 10 and
    date(ENTRY_TS) between
    current date - 7 days and
    current date
    ),
    t_t2 (TMP_ENTRY_TS) as (
    select distinct(date(ENTRY_TS)) from wer.trainer_scorecard
    where
    date(entry_ts) between
    current date - 7 days and
    current date - 1 days or
    date(entry_ts) between
    current date - 35 days and
    current date - 29 days or
    date(entry_ts) between
    current date - 63 days and
    current date - 57 days or
    date(entry_ts) between
    current date - 91 days and
    current date - 85 days or
    date(entry_ts) between
    current date - 119 days and
    current date - 113 days or
    date(entry_ts) between
    current date - 147 days and
    current date - 141 days
    )
    SELECT
    distinct TS.TRAINER_EMPLOYEE_NB,
    rtrim(SD.Last_NM) ||', '|| SD.First_NM as DRIVER_NM,
    (Case when EV.EVENT_START_TYPE_CD = 'TRPRP' then date(EV.EVENT_END_TS) else '2099-12-31' end) as TRPRP_DT,
    TR.PK_TRACTOR_NB,
    SD.EMPLOYEE_SENIORITY_DT,
    TR.CURRENT_COST_DIVISION_CD,
    FT.PK_FLEET_MANAGER_CD,
    TR.CURRENT_TRACTOR_MANAGER_CD,
    TS.TOTAL_SAFETY_SCORE_QY,
    TS.SAFETY_RANKING_QY,
    TS.WEIGHTED_SCORE_QY,
    date(TS.ENTRY_TS) as EntryDate
    from
    wer.EMPLOYEE as SD,
    wer.TRAINER_SCORECARD as TS,
    wer.TRACTOR as TR,
    wer.FLEET_TRACTOR_MANAGER_ASSIGNMENT as FT,
    wer.EMPLOYEE_REFERENCE as ER,
    wer.EVENT as EV
    where
    TS.TRAINER_EMPLOYEE_NB in (
    select TMP_TRAINER_EMPLOYEE_NB from t_t1 ) and
    TS.TRAINER_EMPLOYEE_NB = SD.EMPLOYEE_NB and
    TS.TRAINER_EMPLOYEE_NB = TR.OPERATOR_NB and
    TS.SAFETY_RANKING_QY <> 0 and
    TR.CURRENT_TRACTOR_MANAGER_CD = FT.PK_TRACTOR_MANAGER_CD and
    ER.EMPLOYEE_NB = TS.TRAINER_EMPLOYEE_NB and
    ER.STAKEHOLDER_NB = EV.STAKEHOLDER_NB and
    ER.RELATION_STATUS_CD <> 'I' and
    FT.FLEET_MANAGER_END_DT >= '9998-01-01' and
    TR.CURRENT_COST_DIVISION_CD in ('V') and
    date(TS.ENTRY_TS) in (
    select date(TMP_ENTRY_TS) from t_t2) and
    date(TS.ENTRY_TS) >= '2007-08-01'
    order by
    TRPRP_DT desc,
    EntryDate desc,
    TS.SAFETY_RANKING_QY;

    this query is running on db2 V8.5 on solaris environment.all other parameters seems to be fine like indexes on the tables.its doing few tablescans.but primarly need to modify the query in a better way.

    thank you all.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What I would do is two things.

    First, do not use DATE(entry_ts) between x and y, use entry_ts between timestamp(x,'00:00:00') and timestamp(y,'24:00:00'). Also make sure there is an index on entry_ts.

    Second, instead of doing all of the OR in t_t1, do it as a bunch of UNION ALL

    Both of these should prevent table scanning which seems to be you problem.

    Andy

  3. #3
    Join Date
    Nov 2007
    Posts
    27
    [QUOTE=ARWinner]What I would do is two things.

    First, do not use DATE(entry_ts) between x and y, use entry_ts between timestamp(x,'00:00:00') and timestamp(y,'24:00:00'). Also make sure there is an index on entry_ts.

    Second, instead of doing all of the OR in t_t1, do it as a bunch of UNION ALL

    Both of these should prevent table scanning which seems to be you problem.

    Andy



    Hi Andy thanq for your reply.and can you please help me in using the timestamp . I have tried but could not get the result. so please could you help me by writing that entry_ts format in little detailed way.I really appreciate your help.

    Thanks..

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What you want should look like this:

    with t_t1 (TMP_TRAINER_EMPLOYEE_NB) as (
    select TRAINER_EMPLOYEE_NB from wer.trainer_scorecard
    where SAFETY_RANKING_QY BETWEEN .001 AND 10 and
    ENTRY_TS between timestamp(current date - 7 days,'00:00:00') and
    timestamp(current date.'24:00:00')
    ),

    Andy

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by rajaryan4545
    this query is running on db2 V8.5 on solaris environment.all other parameters seems to be fine like indexes on the tables.its doing few tablescans.but primarly need to modify the query in a better way.
    Also, it *might* possibly help to replace
    Code:
    where
        TS.TRAINER_EMPLOYEE_NB
        in (select TMP_TRAINER_EMPLOYEE_NB from t_t1 )
    by a join, and similary for
    Code:
    and
        date(TS.ENTRY_TS)
        in (select date(TMP_ENTRY_TS) from t_t2)
    At least, compare the two access paths to see whether they differ, and if so, which one is more performant.
    Since you are removing duplicates anyhow ("distinct"), this join will not introduce extra result lines.
    Last edited by Peter.Vanroose; 12-03-07 at 16:36.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Nov 2007
    Posts
    27

    sql

    Thanks andy it worked very well....i appreciate your help....

Posting Permissions

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