By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,779 Members | 1,078 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,779 IT Pros & Developers. It's quick & easy.

Please help to optimize the SQL Query

P: 1
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_BOOK a LEFT OUTER JOIN db2inst1.CNGC_NOTE b
ON a.CNOTE_BOOK_ID = b.CNOTE_BOOK_ID LEFT OUTER JOIN db2inst1.CMCN_SERIES c
ON a.CNOTE_SERIES = c.CODE LEFT OUTER JOIN db2inst1.MKAG_PROFILE 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_OPERATOR IS NULL
Oct 23 '07 #1
Share this Question
Share on Google+
1 Reply


P: 6
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_BOOK a LEFT OUTER JOIN db2inst1.CNGC_NOTE b
ON a.CNOTE_BOOK_ID = b.CNOTE_BOOK_ID LEFT OUTER JOIN db2inst1.CMCN_SERIES c
ON a.CNOTE_SERIES = c.CODE LEFT OUTER JOIN db2inst1.MKAG_PROFILE 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_OPERATOR IS NULL
Nov 2 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.