December 25

9 comments

Teradata Tuning – 70 Times Faster Queries!

By Martin Murtenthaler

December 25, 2019

tuning

teradata tuning
Martin Murtenthaler, Teradata Tuning Specialist at Saturn & Mediamarkt – Germany

The Art of Teradata Performance Tuning

As a Teradata Performance Tuner, you not only need technical knowledge but also a lot of experience. Sometimes also a bit of luck.

With this example, I'll show you how incredible results can be achieved by rewriting a query.

We assume the following scenario:

One table has only a few rows, the other is partitioned and has many rows.
The primary index of both tables is the same.

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 following DELETE statement was executed in the ETL process:

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;

The execution of the DELETE statement took more than 35 minutes, so I first thought about optimizing it.

During real-time monitoring in Viewpoint, I find out that the request was skewed.

The problem was clearly skewed on the column client_id of 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 showed that a sliding window merge join was performed.

The execution plan showed that a sliding window merge join was performed. In principle not a bad choice of the optimiser, but the skew led to a catastrophic result:

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.

My goal was, therefore, to persuade the optimizer to change the execution plan.

After some trial and error I came to 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 new execution plan looked like this:

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

With the new execution plan, the execution of the query took only 25 seconds out of the original 35 minutes!

By rewriting this query, I could force the optimizer to use a sliding window inclusion merge join instead of a regular sliding window merge join. Combined with the EXISTS subquery this highly improved the execution plan, leading to a speedup factor of 70!

Teradata Tuning Conclusion

Query rewriting can be a very powerful tool. But it also has the disadvantage that you have to deal with the content of the query; in contrast to a purely technical optimization, where the query can be considered a black box.

Before rewriting a query, you should make sure that the other requirements are met (statistics, etc.)

Martin Murtenthaler


Martin is an expert in Teradata Performance Tuning having many years of hands-on experience in Data Warehousing. He was involved in some of the major Data Warehouse Sites in Central Europe.

You might also like

  • I think the optimizer does a poor job in this case or the join implementation in TD is not ok. I came across this situation a couple of times even without much skew. There is something wrong with the sliding window merge join.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Never miss a good story!

     Subscribe to our newsletter to keep up with the latest trends!

    >