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.

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

[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..

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

Also, it *might* possibly help to replace by a join, and similary for 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.

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