Tuning with the Teradata TD_WhichMax Function

Roland Wenzlofsky

September 19, 2022

minutes reading time


What is the Teradata TD_WhichMax Function?

Each Teradata release adds new SQL features to make our lives easier. While some provide new functionality, some bring performance benefits through lower resource consumption.

Today we will take a closer look at the Teradata TD_WhichMax and Teradata TD_WhichMin functions and how we can use them to our advantage.

These functions return the minimum (or maximum) value of a column. Usually, such a SQL is used for this purpose:

SELECT * FROM DWHPRO.TESTTABLE
WHERE ID IN 
(
   SELECT MAX(ID)
   FROM  DWHPRO.TESTTABLE
);

Below is the associated Execution Plan:

3) We do an all-AMPs SUM step in TD_MAP1 to aggregate from
     DWHPRO.TESTTABLE by way of an all-rows scan with no
     residual conditions, grouping by field1 (25570).  Aggregate
     intermediate results are computed globally, then placed in Spool 2
     in TD_Map1.  The input table will not be cached in memory, but it
     is eligible for synchronized scanning.  The size of Spool 2 is
     estimated with high confidence to be 1 row (19 bytes).  The
     estimated time for this step is 44.87 seconds.
  4) We do an all-AMPs RETRIEVE step in TD_Map1 from Spool 2 (Last Use)
     by way of an all-rows scan into Spool 6 (all_amps) (compressed
     columns allowed), which is duplicated on all AMPs in TD_Map1.  The
     size of Spool 6 is estimated with high confidence to be 216 rows (
     3,672 bytes).  The estimated time for this step is 0.01 seconds.
  5) We do an all-AMPs JOIN step in TD_Map1 from Spool 6 (Last Use) by
     way of an all-rows scan, which is joined to
     DWHPRO.TESTTABLE by way of an all-rows scan with no
     residual conditions.  Spool 6 and DWHPRO.TESTTABLE are
     joined using a product join, with a join condition of (
     "DWHPRO.TESTTABLE.ID = Field_2").  The input
     table DWHPRO.TESTTABLE will not be cached in memory,
     but it is eligible for synchronized scanning.  The result goes
     into Spool 1 (group_amps), which is built locally on the AMPs.
     The size of Spool 1 is estimated with no confidence to be 67,590
     rows (46,096,380 bytes).  The estimated time for this step is
     33.51 seconds.
  6) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 1 minute and 18 seconds.

In our example, the table “TESTTABLE” has 5 billion rows; the column “ID” is not the primary index.

As an alternative, Teradata now also offers the TD_WhichMax (or TD_WhichMin) functions, which provide the same functionality:

SELECT * FROM TD_WhichMax (
 ON DWHPRO.TESTTABLE AS InputTable
  USING  TargetColumn ('ID')
AS EVENT;

Below is the associated Execution Plan:

  3) We do an all-AMPs SUM step in TD_MAP1 to aggregate from
     DWHPRO.TESTTABLE by way of an all-rows scan with no
     residual conditions, grouping by field1 (-5412).  Aggregate
     intermediate results are computed globally, then placed in Spool 4
     in TD_Map1.  The input table will not be cached in memory, but it
     is eligible for synchronized scanning.  The size of Spool 4 is
     estimated with high confidence to be 1 row (19 bytes).  The
     estimated time for this step is 44.87 seconds.
  4) We do an all-AMPs RETRIEVE step in TD_Map1 from Spool 4 (Last Use)
     by way of an all-rows scan into Spool 2 (all_amps), which is built
     locally on the AMPs.  The size of Spool 2 is estimated with high
     confidence to be 1 row (17 bytes).  The estimated time for this
     step is 0.00 seconds.
  5) We do an all-AMPs DISPATCHER RETRIEVE step in TD_Map1 from Spool 2
     (Last Useby way of an all-rows scan and send the rows back to
     the Dispatcher.  The size is estimated with high confidence to be
     1 row.  The estimated time for this step is 0.00 seconds.
  6) We do an all-AMPs RETRIEVE step in TD_MAP1 from
     DWHPRO.TESTTABLE by way of an all-rows scan with a
     condition of ("DWHPRO.TESTTABLE.ID = :%SSQ25")
     into Spool 1 (group_amps), which is built locally on the AMPs.
     The input table will not be cached in memory, but it is eligible
     for synchronized scanning.  The result spool file will not be
     cached in memory.  The size of Spool 1 is estimated with no
     confidence to be 456,832,786 rows (311,559,960,052 bytes).  The
     estimated time for this step is 2 minutes and 53 seconds.
-> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 3 minutes and 38 seconds.

Here are the performance metrics:

QueryBandIOCPUSpool
Traditional7730368,006128,568519680
TD_WhichMax7727713,005583,914419584

As you can see, the number of IOs is almost the same, and the CPU seconds are a little better with the TD_WhichMax function. However, the TD_WhichMax function needs only half as much spool space as the traditional SQL (The product join doubles the required spool space for the conventional query).

Can we conclude that the Teradata TD_WhichMax function always performs better than the traditional method using a subquery? No, because it also depends on the extent to which the Optimizer can use the primary index in the execution plan. Therefore here is another comparison of both queries again, but this time the column “ID” is the primary index of the table:

QueryBandIOCPUSpool
Traditional3863543,003191,438192
TD_WhichMax3863478,003075,528192

As you can see, all performance metrics are almost the same once the query is done retrieving via the Primary Index.

You can find more about the Teradata TD_WhatMax and Teradata TD_WhatMin functions here:

TD_WhatMax and TD_WhatMin

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

You might also like

>