SQL Qery Performance Problem

Database

SQL Qery Performance Problem

Hi I have tp speed up this query. Please help. right now its taking more than 30 min.

SELECT
Q.CNTCT_ID
,Q.INSTNC_ID
,Q.CMPGN_RUN_DT
,Q.CMPGN_RUN_TM
,Q.CMPGN_RUN_DATE
,Q.TRKING_ID
,Q. USER_ID
,Q.ITEM_ID
,Q.CONV_TYPE_ID
,Q. ACTIVITY_DATE
,Q.AUCT_END_DT
,Q.CSTMZD_LINK_TRKING_DURTN
,LSTG.LEAF_CATEG_ID
,Q.SITE_ID
,Q.TRTMNT_TYPE_CODE
FROM
(
SELECT
CC.CNTCT_ID CNTCT_ID
,CC.INSTNC_ID INSTNC_ID
,CC.CMPGN_RUN_DT CMPGN_RUN_DT
,CC.CMPGN_RUN_TM CMPGN_RUN_TM
,CC.CMPGN_RUN_DATE CMPGN_RUN_DATE
,CC.CMPGN_SENT_DATE
,CC.TRKING_END_DATE
,CC.TRKING_ID TRKING_ID
,CC.USER_ID USER_ID
,WTCH.ITEM_ID ITEM_ID
,3 CONV_TYPE_ID
,WTCH.SRC_CRE_DATE ACTIVITY_DATE
,WTCH.AUCT_END_DT AUCT_END_DT
,CC.CSTMZD_LINK_TRKING_DURTN
CSTMZD_LINK_TRKING_DURTN
,WTCH.SITE_ID SITE_ID
,CC.TRTMNT_TYPE_CODE TRTMNT_TYPE_CODE

FROM SUMM_W CC
JOIN WTCH_TRK WTCH
ON CC.USER_ID = WTCH.WTCHR_ID
QUALIFY WTCH.SRC_CRE_DATE = MIN(WTCH.SRC_CRE_DATE)
OVER(PARTITION BY WTCH.ITEM_ID,WTCH.WTCHR_ID)
WHERE WTCH.SRC_CRE_DATE BETWEEN CC.CMPGN_SENT_DATE AND
CC.TRKING_END_DATE
) Q
,LSTG_ITEM LSTG
,MODEL_SA_MAP CFG
WHERE LSTG.ITEM_ID = Q.ITEM_ID
AND LSTG.AUCT_END_DT = Q.AUCT_END_DT
AND CFG.AUCT_TYPE_CODE = LSTG.AUCT_TYPE_CODE
AND CFG.INCLD_YN_ID = 1
AND CFG.MODEL_SA_CODE = 'CMC'
AND LSTG.AUCT_END_DT >= '${min_cmpgn_sent_dt}' ;
3 REPLIES

Re: SQL Qery Performance Problem

Hi SPS,
try below sql, and let me know result

SELECT
Q.CNTCT_ID
,Q.INSTNC_ID
,Q.CMPGN_RUN_DT
,Q.CMPGN_RUN_TM
,Q.CMPGN_RUN_DATE
,Q.TRKING_ID
,Q. USER_ID
,Q.ITEM_ID
,Q.CONV_TYPE_ID
,Q. ACTIVITY_DATE
,Q.AUCT_END_DT
,Q.CSTMZD_LINK_TRKING_DURTN
,LSTG.LEAF_CATEG_ID
,Q.SITE_ID
,Q.TRTMNT_TYPE_CODE
FROM
(
SELECT
CC.CNTCT_ID CNTCT_ID
,CC.INSTNC_ID INSTNC_ID
,CC.CMPGN_RUN_DT CMPGN_RUN_DT
,CC.CMPGN_RUN_TM CMPGN_RUN_TM
,CC.CMPGN_RUN_DATE CMPGN_RUN_DATE
,CC.CMPGN_SENT_DATE
,CC.TRKING_END_DATE
,CC.TRKING_ID TRKING_ID
,CC.USER_ID USER_ID
,WTCH.ITEM_ID ITEM_ID
,3 CONV_TYPE_ID
,WTCH.SRC_CRE_DATE ACTIVITY_DATE
,WTCH.AUCT_END_DT AUCT_END_DT
,CC.CSTMZD_LINK_TRKING_DURTN
CSTMZD_LINK_TRKING_DURTN
,WTCH.SITE_ID SITE_ID
,CC.TRTMNT_TYPE_CODE TRTMNT_TYPE_CODE

FROM SUMM_W CC
JOIN WTCH_TRK WTCH
ON CC.USER_ID = WTCH.WTCHR_ID
QUALIFY WTCH.SRC_CRE_DATE = MIN(WTCH.SRC_CRE_DATE)
OVER(PARTITION BY WTCH.ITEM_ID,WTCH.WTCHR_ID)
WHERE WTCH.SRC_CRE_DATE BETWEEN CC.CMPGN_SENT_DATE AND
CC.TRKING_END_DATE
) Q
,LSTG_ITEM LSTG
,MODEL_SA_MAP CFG

WHERE CFG.AUCT_TYPE_CODE = LSTG.AUCT_TYPE_CODE
AND CFG.INCLD_YN_ID = 1
AND CFG.MODEL_SA_CODE = 'CMC'
AND LSTG.AUCT_END_DT >= '${min_cmpgn_sent_dt}'
AND LSTG.ITEM_ID = Q.ITEM_ID
AND LSTG.AUCT_END_DT = Q.AUCT_END_DT ;

FYI: for sql performance tuning lots of issure needed to take care
table size, Indexes(primary and Secondary), precedence of Residual conditions in Joins etc... hence try the above query.

I have changed just the order or joining the tables, if you can filter the more and more rows initially then joining will take less space and CPU time both.

take care....
Regards,

Re: SQL Qery Performance Problem

I am not sure if it should matter to move the joins around. Best thing would be to run an explain with "diagnostics on" and then collect the statistics this will make a huge difference. The planner should take care of which table to join first, which table to distribute and so on ...

Re: SQL Qery Performance Problem

Do you have an explain plan for the current query?