 |
 |
Archives of the TeradataForum
Message Posted: Thu, 03 Jun 2004 @ 13:10:27 GMT
| Subj: | | Performance problem while creating a volatile table |
| |
| From: | | Kambhoji, Naveen Ram Prasanna |
Hi,
I dont know the use of this table. All I need to do is to tune this .....Currently it is taking 15 hours to run resulting in 100 Million rows.
I am new to the Teradata . Can any one help me out in what way I can tune this query.
CREATE VOLATILE TABLE TMP_AD_SEGMENT_2 as
(SELECT row_number() over(order by 1) as Seqno,
ma.MarketID,
17 as MediaSubTypeID,
dt.DateID,
ad.DaypartCode,
locmcl.CurrentCompanyID as CompanyID,
CASE
WHEN a.AdvertiserTypeID = 2 THEN -1
ELSE av.AdvertiserID
END as AdvertiserID,
dv.DistributorID,
pv.ProgramID,
cr.CreativeID,
COALESCE(cl.ClassificationID,0) as ClassificationID,
ad.AttributeID,
SUM((s.HHLDS * ma.TVAudienceIndex * lu.HHLDS_Universe) / nu.HHLDS_Universe) as HHLDS,
SUM((s.F12_17 * ma.TVAudienceIndex * lu.F12_17_Universe) / nu.F12_17_Universe) as F12_17,
SUM((s.F18_20 * ma.TVAudienceIndex * lu.F18_20_Universe) / nu.F18_20_Universe) as F18_20,
SUM((s.F21_24 * ma.TVAudienceIndex * lu.F21_24_Universe) / nu.F21_24_Universe) as F21_24,
SUM((s.F25_34 * ma.TVAudienceIndex * lu.F25_34_Universe) / nu.F25_34_Universe) as F25_34,
SUM((s.F35_54 * ma.TVAudienceIndex * lu.F35_54_Universe) / nu.F35_54_Universe) as F35_54,
SUM((s.F55up * ma.TVAudienceIndex * lu.F55up_Universe) / nu.F55up_Universe) as F55up,
SUM((s.M12_17 * ma.TVAudienceIndex * lu.M12_17_Universe) / nu.M12_17_Universe) as M12_17,
SUM((s.M18_20 * ma.TVAudienceIndex * lu.M18_20_Universe) / nu.M18_20_Universe) as M18_20,
SUM((s.M21_24 * ma.TVAudienceIndex * lu.M21_24_Universe) / nu.M21_24_Universe) as M21_24,
SUM((s.M25_34 * ma.TVAudienceIndex * lu.M25_34_Universe) / nu.M25_34_Universe) as M25_34,
SUM((s.M35_54 * ma.TVAudienceIndex * lu.M35_54_Universe) / nu.M35_54_Universe) as M35_54,
SUM((s.M55up * ma.TVAudienceIndex * lu.M55up_Universe) / nu.M55up_Universe) as M55up
FROM DW_DB.FA_AD ad
JOIN DW_DB.FA_SEGMENT s on s.ADID = ad.ADID
JOIN DW_DB.DISTRIBUTOR_VARIANT dv on dv.DistributorVariantID = ad.DistributorVariantID
JOIN DW_DB.PROGRAM_VARIANT pv on pv.ProgramVariantID = ad.ProgramVariantID
JOIN DW_DB.MARKET_VARIANT mv on mv.MarketVariantID = ad.MarketVariantID
JOIN DW_DB.COMPANY_VARIANT cov on cov.CompanyVariantID = ad.CompanyVariantID
JOIN DW_DB.ADVERTISER_VARIANT av on av.AdvertiserVariantID = ad.AdvertiserVariantID
JOIN DW_DB.DIM_ADVERTISER a on a.AdvertiserID = av.AdvertiserID
JOIN DW_DB.FA_CREATIVE cr on cr.CreativeID = ad.CreativeID
JOIN LU_DATE dt on dt.DateDate = cast(ad.PublishDate as date)
JOIN DW_DB.MARKET_ALLOCATION ma on CAST(ma.StartDate as DATE)
<= dt.DateDate AND CAST(ma.EndDate as DATE) >= dt.DateDate
JOIN DW_DB.DIM_MARKET m on m.MarketID = ma.MarketID
JOIN TMP_UNIVERSE lu on lu.MarketID = ma.MarketID AND lu.StartDateID
<= dt.DateID AND lu.EndDateID >= dt.DateID
JOIN TMP_UNIVERSE nu on nu.MarketID = 53 AND nu.StartDateID
<= dt.DateID AND nu.EndDateID >= dt.DateID
JOIN DW_DB.MARKET_COMPANY_LICENSED natmcl on natmcl.MarketID = mv.MarketID
AND natmcl.CompanyID = cov.CompanyID
AND ad.PublishDate between natmcl.StartDate and natmcl.StopDate
JOIN DW_DB.MARKET_COMPANY_LICENSED locmcl on locmcl.MarketID = ma.MarketID
AND locmcl.CompanyID = natmcl.LocalCompanyID
AND ad.PublishDate BETWEEN locmcl.StartDate AND locmcl.StopDate
LEFT JOIN LU_CLASSIFICATION cl on cl.CreativeID = ad.CreativeID and cl.CompanyID
= natmcl.CurrentCompanyID
WHERE dt.DateID <= 38076
AND ad.MediaSubTypeID = 4
AND cr.CreativeTypeID IN (1,3,4)
AND natmcl.Licensed = 1
AND locmcl.Licensed = 1
AND m.Reported = 1
AND ma.MarketID <> 53
GROUP BY ma.MarketID, dt.DateID, ad.DaypartCode, locmcl.CurrentCompanyID,
CASE
WHEN a.AdvertiserTypeID = 2 THEN -1
ELSE av.AdvertiserID
END, dv.DistributorID, pv.ProgramID, cr.CreativeID, cl.ClassificationID, ad.AttributeID
)WITH DATA PRIMARY INDEX(Seqno)
ON COMMIT PRESERVE ROWS;
thanx & regards,
Prasanna.
| |
