Please help to optimize the SQL Query

[kmugunda]

Hi below is a query where certain set of data is fetched from a table based on certain condition, but the execution takes lot of time , is there a way we can optimize the code below


SELECT DISTINCT a.CNOTE_BOOK_ID , a.CNOTE_SERIES, a.AGENT_CODE, a.OPERATOR
FROM db2inst1.CNGC_B OOK a LEFT OUTER JOIN db2inst1.CNGC_N OTE b
ON a.CNOTE_BOOK_ID = b.CNOTE_BOOK_ID LEFT OUTER JOIN db2inst1.CMCN_S ERIES c
ON a.CNOTE_SERIES = c.CODE LEFT OUTER JOIN db2inst1.MKAG_P ROFILE d
ON a.AGENT_CODE =d.AGENT_CODE

WHERE b.EASC_DDATE IS NULL AND c.EASC_IND = 'Y' AND d.EAMS_IND = 'Y' AND a.AGENT_CODE NOT LIKE '%00007'
AND b.EASC_DTIME IS NULL AND b.DLOAD_OPERATO R IS NULL

[ties]

To be helpfull you have to give extra information:
1. Are there any indexes on tables b, c and d (index on join column can really help!) ?
2. Size of tables (approximate number of rows)

As a firstsuggestion : move the a.AGENT_CODE not like '%00007' condition to the start of the where clause. If that condition is false, the rest does not have to be evaluated.

Hi below is a query where certain set of data is fetched from a table based on certain condition, but the execution takes lot of time , is there a way we can optimize the code below


SELECT DISTINCT a.CNOTE_BOOK_ID , a.CNOTE_SERIES, a.AGENT_CODE, a.OPERATOR
FROM db2inst1.CNGC_B OOK a LEFT OUTER JOIN db2inst1.CNGC_N OTE b
ON a.CNOTE_BOOK_ID = b.CNOTE_BOOK_ID LEFT OUTER JOIN db2inst1.CMCN_S ERIES c
ON a.CNOTE_SERIES = c.CODE LEFT OUTER JOIN db2inst1.MKAG_P ROFILE d
ON a.AGENT_CODE =d.AGENT_CODE

WHERE b.EASC_DDATE IS NULL AND c.EASC_IND = 'Y' AND d.EAMS_IND = 'Y' AND a.AGENT_CODE NOT LIKE '%00007'
AND b.EASC_DTIME IS NULL AND b.DLOAD_OPERATO R IS NULL