What results in increased CPU time - An overview

During a query, we may notice increase in the effective CPU usage or Total CPU Usage.

If we closely observe the DBQL tables, we will see the following points:

1. The queries with more complex calculations utilise more CPU
2. If there are more joins, it results in increased CPU
3. Calculation while inserting data will lead to more CPU.
     For example:

Insert into TEST
Select
Cus_id
,Max(Day_nbr) +1
,Amount
,Name
From
Customer;

This is because the operation will be done on spool. There is a overhead associated with each insert.

4. If the data is skewed, we will see the total CPU rise.

   This is because, in Teradata, each AMP performs its calculations independently.
Now,say an Aggregation is happening on each AMP. If one of the AMP is skewed, it will take more time to complete the operation. All the remaining AMPS will wait for this AMP.

This difference in TOTAL_AMP_CPU  and EFFECTIVE_CPU is referred to as Parallel Efficiency.

5. Use of SET tables lead to higher AMP CPU. It is because SET tables have a row level check on duplicates.