0

I am trying to Update a huge table in Teradata on a daily basis. The Update Statement is taking a lot of AMPCPUTime.

Table contains 65 Billion rows and 100-200 Million Rows are updated.

Table is a Set Table with Non Unique PI. The data distribution is quite even with 0.8 Skew Factor.

What is the way to reduce the AMPCPU Time?

The Update is done using a Stage table. Join is on a subset of PI columns.

Attempts: Changed the PI of stage table same as Target Table. Explain PLan says a Merge Update is being performed. But AMPCPUTime is rather increasing. Tried Delete and Insert but Delete and Insert also taking greater AMPCPUTime.

Ankit Srivastava
  • 185
  • 3
  • 13
  • What's the average and max number of rows per PI? Did you try MERGE instead? Is the targe tabble partitioned? – dnoeth Sep 07 '20 at 16:45
  • Average and Max is similar 9.42 M & 9.53 M respectively. Merge is also taking similar AMPCPU Time. Target Table is not partitioned – Ankit Srivastava Sep 07 '20 at 17:55
  • A NUPI on a SET table with almost 10 million rows per value? Ouch. Of course this needs lots of CPU, you update a single row and the filesystem has to check if it results in a Duplicate Row. Insert should be similar, using lots of CPU. – dnoeth Sep 07 '20 at 20:09
  • Tried converting the target table into MultiSet. But the AMPCPU is still higher. And 10 M is per AMP records based on the PI – Ankit Srivastava Sep 07 '20 at 20:11
  • Ok, but what is the number of rows per PI-value then? Any Secondary/Join/Hash Indices,FKs? Block Level Compression? – dnoeth Sep 07 '20 at 20:16
  • Per PI value based on 1/10th data sample its 1.01 and Max 2... No Secondary/Join/Hash Indices and BLC – Ankit Srivastava Sep 07 '20 at 20:35
  • Btw, the easiest way to get this info is from statistics, there should be stats on the PI. Can you compare CPU vs. a simple count(nullable_column)? – dnoeth Sep 07 '20 at 20:42

0 Answers0