Archives of the TeradataForum

Message Posted: Thu, 15 Aug 2001 @ 16:36:20 GMT

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

Hi Trevor:

There really isn't enough data here to come to a firm conclusion. I say this because you write about a table with an eleven column UPI, but I do not see where it is used.

However, I do see two things that might account for the slow operation.

The first thing I would attempt is to eliminate the conversions because normally they are slow. All of your input values are CHAR. I am assuming that the columns in the tables are not CHAR. Therefore, Teradata needs to convert every column of every row to CHAR in order to make the comparison. THIS CAN BE VERY SLOW. I would find a way to make the input data the same type as the comparison. If needed, load them to a table and do the conversion there.

The second thing is easier, but not as large a potential for gain. It is to move acc_no to the first column (as shown below) in the volatile table. Normally, Teradata uses the first column as a NUPI whenever a PI or PK are not defined. This should cause a different distributiion.

CREATE VOLATILE TABLE XNET_CUST1
   ,ACC_NO            INTEGER
   (cust_id           INTEGER
   ,STD               CHAR(4)
   ,TELNO             CHAR(7)
   ,COUNTRY_DESC      CHAR(20)
   ,SUM_CALLS         INTEGER
   ,SUM_DURATION      INTEGER
   ,YEAR_MONTH        INTEGER)

 ON COMMIT PRESERVE ROWS;

 INSERT INTO XNET_CUST1
 SELECT     FP.ACC_NO
           ,T.CUST_ID
           ,FP.STD
           ,FP.TELNO
           ,FP.COUNTRY_CODE
           ,SUM(FP.SUM_CALLS)
           ,(SUM(FP.SUM_DURATION(DECIMAL (15)))) / 60
           ,FP.YEAR_MONTH
 FROM       CDW_PROD_TEMP.XNET_CUST             T   JOIN
            CDW_PROD_BV.FP_TRAFFIC_SUM_V        FP
 where      T.ACC_NO = FP.ACC_NO
 and        fp.country_code not in ('44','353')
 GROUP BY   1,2,3,4,5,8
 ;

I hope either of these, or both help you. Otherwise, we will probably need more detail information about the tables used in the requests.

Regards and good luck,

Mike

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