Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 03 Jun 2004 @ 13:38:20 GMT


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


Subj:   Re: Performance problem while creating a volatile table
 
From:   Dieter Noeth

Naveen Ram Prasanna Kambhoji wrote:

  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,
......
         )WITH DATA PRIMARY INDEX(Seqno)
         ON COMMIT PRESERVE ROWS;

"order by 1" causes the intermediate spool used to create the SeqNo to be located on a single AMP (probably AMP 0). That AMP has a huge and extremly skewed spool and uses a single CPU for 100% for 15 hours.

Just use any column with lots of distinct values in "order by".

And because row_number is unique modify the PI to "UNIQUE PRIMARY INDEX(Seqno)".

But it looks as SeqNo is created only for distribution, do you really use it in Where or Joins?

If not, remove it and use a different PI, e.g. all

     UNIQUE PRIMARY INDEX
     (MarketID, DateID, DaypartCode, CurrentCompanyID, AdvertiserID,
       DistributorID, ProgramID, CreativeID, ClassificationID, AttributeID)

or any combination providing a good distribution as NUPI

Or just use a RANDOM(-2147483648,2147483647) as SeqNo:

This will be not unique, but almost... And much faster, because there's no Stat Function Step


Dieter



     
  <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