Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 03 Jun 2004 @ 13:10:27 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


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.



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016