Query Tuning Help

[reachsamdurai]

Greetings,
I have the following query which takes very long time to execute hence
can you please help to fine tune it as I'm new to DB2 World. Thanks

SELECT
char(VEH_IDENT_NBR) as VEH_IDENT_NBR,
OPTN_CD,
SRC_TRMNT_TIMSTM,
DWH_EFCTV_TIMSTM,
DWH_UPD_TIMSTM
FROM
CARD.VIN_VEH_OPTNS a
WHERE
(
a.DWH_EFCTV_TIMSTM >
(
SELECT
MAX(DWH_ETL_ST_TIMSTM)
FROM
card.DWH_CONTROL
where
DWH_SEQ_NM = 'a' and
DWH_ETL_STATUS='S'
)
OR
a.DWH_UPD_TIMSTM >
(
SELECT
MAX(DWH_ETL_ST_TIMSTM)
FROM
card.DWH_CONTROL
where
DWH_SEQ_NM ='a' and
DWH_ETL_STATUS='S'
)
)
and
VEH_OPTN_CD_CSI=1

CARD.VIN_VEH_OPTNS Table details
- Has 58 Million rows
- Partitioned table based on VEH_IDENT_NBR
- Indexes & Keys
1. INDEX on ( DWH_UPD_TIMSTM ASC,VEH_IDENT_NBR
ASC,OPTN_CD ASC,VEH_OPTN_CD_CSI
ASC)
2. PRIMARY KEY (VEH_IDENT_NBR, OPTN_CD, VEH_OPTN_CD_CSI);

CARD.DWH_CONTROL
- Small table with 2000 rows
- Non-partitioned table

Regards,
Sam

[tuarek]

Sam,

I just reorganized your statement. Select stmt in your where clause
seemed repeating. But I am not sure how this would affect overall
performance. Give it a try and let me know your results.

regards,

Mehmet

SELECT
char(VEH_IDENT_NBR) as VEH_IDENT_NBR,
OPTN_CD,
SRC_TRMNT_TIMSTM,
DWH_EFCTV_TIMSTM,
DWH_UPD_TIMSTM
FROM
CARD.VIN_VEH_OPTNS a,

( SELECT MAX(DWH_ETL_ST_TIMSTM) as tempfield
FROM card.DWH_CONTROL
where DWH_SEQ_NM = 'a' and DWH_ETL_STATUS='S' ) as temptable

WHERE
(
a.DWH_EFCTV_TIMSTM > temptable.tempfield

OR
a.DWH_UPD_TIMSTM > temptable.tempfield

)
and
VEH_OPTN_CD_CSI=1

[reachsamdurai]

Thanks for your query. But after adding a new index on VEH_OPTN_CD_CSI
I have got a same performance using both the query. The access plan is
as below and I'm still looking for options to improve the performance.

Access Plan:
-----------
Total Cost: 335820
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1.4601e+07
DTQ
( 2)
335820
180963
|
4.867e+06
NLJOIN
( 3)
331100
180963
/------+------\
1 4.867e+06
GRPBY FETCH
( 4) ( 9)
12.952 331087
1 180962
| /----+---\
3 6.4895e+06 1.94685e+07
BTQ IXSCAN TABLE: CARD
( 5) ( 10) VIN_VEH_OPTNS
12.9516 25082.3
1 11149.1
| |
1 1.94685e+07
GRPBY INDEX: CARD
( 6) I3
12.891
1
|
1
FETCH
( 7)
12.8908
1
/----+---\
1 14
IXSCAN TABLE: CARD
( 8) DWH_CONTROL
0.0331973
0
|
14
INDEX: SYSIBM
SQL0602122312521

Thanks,
Sam

[tuarek]

Sam,

What about indexes on DWH_SEQ_NM and DWH_ETL_STATUS fields?

Regards,

Mehmet

[reachsamdurai]

Mehmet, I dont have any index on DWH_CONTROL table apart the primary
key on DWH_LOAD_ID and DWH_SEQ_NM. Also this has around 1000 rows so it
doesn't matter I guess.

Thanks,
Sam

[tuarek]

Sam,

Sorry for overlooking your first postl. Other than your 2 nested query
and being run on a partioned table, the main query is so straight
forward. With indexes on DWH_UPD_TIMSTM and VEH_OPTN_CD_CSI, your query
should really rock. I

When you replace the nested queries with scalars, shouldn't you get the
bare minimum cost? If the cost is still big then you may need to do
change the partion key?

regards,

mehmet