|
|
|
Forum Newbie
Group: Forum Members
Last Login: 7/25/2007 10:30:00 AM
Posts: 7,
Visits: 1
|
|
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}' ;
SPS
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 2/18/2008 4:01:01 AM
Posts: 30,
Visits: 14
|
|
well ,you may list the excute plan first
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 10/11/2006 6:05:00 AM
Posts: 23,
Visits: 1
|
|
|
without seeing the explain or the table stats, my first plan would always be to ditch the OLAP processing.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 7/31/2007 5:20:00 AM
Posts: 12,
Visits: 1
|
|
... 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}'
;
Georg Gottfried
Teradata Certified Design Architect V2R5
Vienna, Austria
|
|
|
|