Mastering Teradata Performance Tuning

The Art of Teradata Performance Tuning

As a Teradata Performance Tuner, technical expertise and experience are essential, occasionally accompanied by fortuitous circumstances.

I’ll demonstrate the remarkable outcomes that can be attained by rephrasing a query using this example.

Assuming this scenario:

One table has a minimal number of rows, while the other is partitioned and consists of numerous rows. Both tables share the same primary index.

CREATE MULTISET TABLE DB1.smalltable 
( 
  CLIENT_ID INTEGER, 
  CALL_START_DATE DATE FORMAT 'YYYY-MM-DD', 
  HARDWARE_ID CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC) 
PRIMARY INDEX ( CLIENT_ID )
;

CREATE MULTISET TABLE DB2.bigtable 
( 
  CLIENT_ID INTEGER, 
  START_DATE DATE FORMAT 'YYYY-MM-DD', 
  END_DATE DATE FORMAT 'YYYY-MM-DD', 
  HARDWARE_ID CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC) 
PRIMARY INDEX ( CLIENT_ID ) 
PARTITION BY CASE_N(END_DATE = DATE '3999-12-31', 
END_DATE = DATE '3999-12-30', NO CASE OR UNKNOWN);

The ETL process executed the DELETE statement.

DELETE a FROM 
DB1.smalltable a, DB2.bigtable c 
where a.call_start_date between c.START_DATE and c.END_DATE and a.CLIENT_ID=c.CLIENT_ID and
a.hardware_id=c.hardware_id;

Running the DELETE statement took over 35 minutes, so I first considered optimizing it.

While monitoring Viewpoint, I found that the request was skewed.

There was a skew issue with the client_id column in the small unpartitioned table.

COLLECT STATISTICS COLUMN ( CLIENT_ID ) ON DB1. smalltable VALUES 
( /** SummaryInfo **/ /* Data Type and Length: 'I:4' */ /* TimeStamp */ TIMESTAMP '2015-03-30 10:55:22-00:00',
 /* Version */ 5,
 /* OriginalVersion */ 5,
 /* DBSVersion */ '14.10.04.09',
 /* UsageType */ 'D',
 /* ComplexStatInfo */ 'ComplexStatInfo',
 /* NumOfBiasedValues */ 54,
 /* NumOfEHIntervals */ 200,
 /* NumOfHistoryRecords */ 0,
 /* SamplePercent */ 0.00,
 /* NumOfNulls */ 0,
 /* NumOfAllNulls */ 0,
 /* NumOfPartialNullVals */ 0,
 /* PartialNullHMF */ 0,
 /* AvgAmpRPV */ 0.000000,
 /* MinVal */ 1250347405,
 /* MaxVal */ 1770591815,
 /* ModeVal */ 1266076019,
 /* HighModeFreq */ 1316929,
 /* NumOfDistinctVals */ 61302,
 /* NumOfRows */ 1744994,
 /* CPUUsage */ 0.000000,
 /* IOUsage */ 0.000000,
 /* Reserved */ 0,
 /* Reserved */ 0,
 /* Reserved */ 0.000000,
 /* Reserved */ 0.000000,
 /* Reserved */ '',
 /** Biased: Value,
 Frequency **/ /* 1 */ 1111111101, 1788,
 /* 2 */ 1111111102, 580,
 /* 3 */ 1111111103, 1826,
 /* 4 */ 1111111104, 810,
 /* 5 */ 1111111105, 1795,
 /* 6 */ 1111111106, 618,
 /* 7 */ 1111111107, 531,
 /* 8 */ 1111111108, 516,
 /* 9 */ 1111111111, 1316929,
 -- HIGHLY SKEWED VALUE !!! /* 10 */ 1111111109,
 543, /* 11 */ 1111111110,
 629, /* 12 */ 1111111112,
 1111, /* 13 */ 1111111113,
 1079, /** Interval: 
 MaxVal,
 ModeVal,
 ModeFreq,
 LowFreq,
 OtherVals,
 OtherRows **/ …

The execution plan revealed the utilization of a sliding window merge join. While this may have been a suitable decision by the optimizer, the skew caused a catastrophic outcome.

1) First, we lock a distinct DB1."pseudo table" for write on a RowHash to prevent global deadlock for DB1.a.
2) Next, we lock a distinct DB2."pseudo table" for read on a RowHash to prevent global deadlock for DB2.c.
3) We lock DB1.a for write, and we lock DB2.c for read.
4) We do an all-AMPs JOIN step from DB2.c by way of a RowHash match scan with no residual conditions, which is joined to DB1.a by way of a RowHash match scan with no residual conditions. DB2.c and DB1.a are joined using a sliding-window merge join, with a join condition of ( "(DB1.a.CLIENT_ID = DB2.c.CLIENT_ID) AND ((DB1.a.HARDWARE_ID = DB2.c.HARDWARE_ID) AND ((DB1.a.CALL_START_DATE >= DB2.c.START_DATE ) AND (DB1.a.CALL_START_DATE <= DB2.c.END_DATE )))"). The result goes into Spool 1 (all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 1 by the hash code of ( DB1.a.ROWID) the sort key in spool field1 eliminating duplicate rows. The size of Spool 1 is estimated with low confidence to be 1,749,441 rows (31,489,938 bytes). The estimated time for this step is 2.20 seconds.
5) We do an all-AMPs MERGE DELETE to DB1.a from Spool 1 (Last Use) via the row id. The size is estimated with low confidence to be 1,749,441 rows. The estimated time for this step is 16 minutes and 34 seconds.
6) We spoil the parser's dictionary cache for the table.
7) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request. -> No rows are returned to the user as the result of statement 1.

I sought to convince the optimizer to modify the execution plan.

After experimenting, I devised the following solution:

delete a from 
DB1.smalltable a, 
DB2.bigtable c 
where a.call_start_date between c.START_DATE and c.END_DATE and coalesce(a.CLIENT_ID,a.CLIENT_ID)=c.CLIENT_ID and a.hardware_id=c.hardware_id and 
exists(select 1 from DB1.smalltable x where x.CLIENT_ID=c.CLIENT_ID);

The revised plan appears as follows:

1) First, we lock a distinct DB1."pseudo table" for write on a RowHash to prevent global deadlock for DB1.a.
2) Next, we lock a distinct DB2."pseudo table" for read on a RowHash to prevent global deadlock for DB2.c.
3) We lock DB1.a for write, and we lock DB2.c for read.
4) We execute the following steps in parallel. 1) We do an all-AMPs RETRIEVE step from DB1.a by way of an all-rows scan with a condition of ("NOT (DB1.a.HARDWARE_ID IS NULL)") into Spool 2 (all_amps), which is redistributed by the hash code of ( DB1.a.HARDWARE_ID, (CASE WHEN (NOT (DB1.a.CLIENT_ID IS NULL )) THEN (DB1.a.CLIENT_ID) ELSE (DB1.a.CLIENT_ID) END )(INTEGER)) to all AMPs. The size of Spool 2 is estimated with high confidence to be 1,744,994 rows ( 82,014,718 bytes). The estimated time for this step is 0.25 seconds. 2) We do an all-AMPs JOIN step from DB2.c by way of an all-rows scan with no residual conditions, which is joined to DB1.x by way of an all-rows scan with no residual conditions. DB2.c and DB1.x are joined using a sliding-window inclusion merge join, with a join condition of ("(DB1.x.CLIENT_ID = DB2.c.CLIENT_ID) AND (NOT (DB2.c.CLIENT_ID IS NULL ))"). The result goes into Spool 3 (all_amps), which is redistributed by the hash code of (DB2.c.CLIENT_ID, DB2.c.HARDWARE_ID) to all AMPs. The size of Spool 3 is estimated with low confidence to be 12,265,080 rows (551,928,600 bytes). The estimated time for this step is 7.92 seconds.
5) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an all-rows scan, which is joined to Spool 3 (Last Use) by way of an all-rows scan. Spool 2 and Spool 3 are joined using a single partition hash join, with a join condition of ("((( CASE WHEN (NOT (CLIENT_ID IS NULL )) THEN (CLIENT_ID) ELSE (CLIENT_ID) END ))= CLIENT_ID) AND ((CALL_START_DATE <= END_DATE ) AND ((CALL_START_DATE >= START_DATE ) AND (HARDWARE_ID = HARDWARE_ID )))"). The result goes into Spool 1 (all_amps), which is redistributed by the hash code of ( DB1.a.ROWID) to all AMPs. Then we do a SORT to order Spool 1 by row hash and the sort key in spool field1 eliminating duplicate rows. The size of Spool 1 is estimated with low confidence to be 1,744,994 rows (31,409,892 bytes). The estimated time for this step is 18.09 seconds.
6) We do an all-AMPs MERGE DELETE to DB1.a from Spool 1 (Last Use) via the row id. The size is estimated with low confidence to be 1,744,994 rows. The estimated time for this step

The query execution time was reduced to 25 seconds with the new execution plan, down from the original 35 minutes.

Rewriting the query to use a sliding window inclusion merge join, and the EXISTS subquery resulted in a significant speedup factor of 70 in the execution plan.

Teradata Tuning Conclusion

Query rewriting is a powerful tool, but it requires handling the query’s content, unlike technical optimization, which treats the query as a black box.

Before revising a query, confirm that all other requirements, such as statistics, have been fulfilled.