SQL Qery Performance Problem

Tools & Utilities
Enthusiast

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
Enthusiast

Re: SQL Qery Performance Problem

well ,you may list the excute plan first :-)
Enthusiast

Re: SQL Qery Performance Problem

without seeing the explain or the table stats, my first plan would always be to ditch the OLAP processing.
gg
Enthusiast

Re: SQL Qery Performance Problem

... and if it is OLAP processing. Try WHERE EXISTS instead ...

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

/* modified SQL code START */
AND WTCH.SRC_CRE_DATE between CC.CMPGN_SENT_DATE and CC.TRKING_END_DATE
WHERE EXISTS (SELECT *
FROM (SELECT
WTCHR_ID
,ITEM_ID
,MIN(WTCH.SRC_CRE_DATE) as MIN_SRC_CRE_DATE
FROM WTCH_TRK
GROUP BY 1,2
) WTCH2
WHERE WTCH.SRC_CRE_DATE = WTCH2.MIN_SRC_CRE_DATE
AND WTCH.WTCHR_ID = WTCH2.WTCHR_ID
AND WTCH.ITEM_ID = WTCH2.ITEM_ID
)
/* modified SQL code END */

) 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}'
;