Hi,

One of my query is taking a lot of execution time.I need help with fine tuning this query.

Query:

SELECT
A,
B,
C,
D,
E
FROM
QUAL1.TABLE1
WHERE
PROCS_TIMSTM > = TIMESTAMP (?) AND
PROCS_TIMSTM < = TIMESTAMP (?) AND
AGENT_CD = ? AND
( STORE_CD IN (?, ?, ?, ?) OR DLV_STORE_CD IN (?, ?, ?, ?) ) WITH UR;

Is there any better way to write this query using sub routines or unions to get better performance.

Thanks in advance

Without knowing what indexes you have on the table (I will assume you have the correct ones), I woud try this:

SELECT
A,
B,
C,
D,
E
FROM
QUAL1.TABLE1
WHERE
PROCS_TIMSTM > = TIMESTAMP (?) AND
PROCS_TIMSTM < = TIMESTAMP (?) AND
AGENT_CD = ? AND
STORE_CD IN (?, ?, ?, ?)

UNION ALL

SELECT
A,
B,
C,
D,
E
FROM
QUAL1.TABLE1
WHERE
PROCS_TIMSTM > = TIMESTAMP (?) AND
PROCS_TIMSTM < = TIMESTAMP (?) AND
AGENT_CD = ? AND
DLV_STORE_CD IN (?, ?, ?, ?)
WITH UR;

Index 1:

AGENT_CD
PROCS_TIMSTM
SELL_SRC_CD
MDL_NBR

index 2:

DLV_STORE_CD
PROCS_TIMSTM
SELL_SRC_CD
MDL_NBR

We can create new indexes if required based on query..

It is not possible to know which indexes should be created based on the information you supplied. For example, I would need to know the cardinality of AGENT_CD to know if it should be indexed.

Since I assume that PROCS_TIMSTM has a very high cardinality, I would make it the first column of at least one of the indexes.

In general, columns with a low cardinality, especially less than 20 unique values, should not be indexed, or not the first column of a multi-column index.

I hope you have run runstats with following options:
runstats on table QUAL1.TABLE1 with distribution on key columns and indexes all