Teradata Tuning with PERIOD Data Type

Roland Wenzlofsky

September 13, 2022

minutes reading time


This article will show a case study from my past projects. It is an implementation in which, from a row per primary key that contains a start date and an end date, rows are to be generated for all days in between. I have simplified the query for demonstration purposes, but the problem is the same. Here is an example:

Primary KeyStart DateEnd Date
12022-09-122022-09-15
Primary KeyStart DateEnd DateCalculated Date
12022-09-122022-09-152022-09-12
12022-09-122022-09-152022-09-13
12022-09-122022-09-152022-09-14
12022-09-122022-09-152022-09-15

First, let me introduce you to the implementation I had to optimize. The problem was solved with a recursive query and worked well at the beginning. Over time, however, the data grew enormously, which eventually led to the fact that the runtime of the query increased immensely:

SET QUERY_BAND = ‘DWHPRO=RECURSION;’ FOR SESSION;

SELECT PK, START_DATE, END_DATE, CALC_DATE
FROM (
WITH RECURSIVE RecTable (PK,START_DATE,END_DATE,CALC_DATE) AS
(

SELECT t01.PK,t01.START_DATE, DATE AS END_DATE, START_DATE AS CALC_DATE
FROM PERFTEST t01
WHERE START_DATE BETWEEN DATE’1900-01-01′ AND DATE+1

UNION ALL

SELECT PK,START_DATE,END_DATE,CALC_DATE + INTERVAL ‘1’ DAY AS CALC_DATE
FROM RecTable
WHERE (CALC_DATE < END_DATE )
)
SELECT * FROM RecTable
) A
ORDER BY PK, CALC_DATE;

QueryBandTotalIOCountAMPCPUTimeSpoolUsageStartTimeFirstRespTime
RECURSION700180812,0038859,8234842068172813.09.2022 19:11:11.83000013.09.2022 19:17:28.820000

Below you can see the optimized query:

SET QUERY_BAND = ‘DWH=EXPAND;’ FOR SESSION;

SELECT PK, START_DT, DATE AS END_DT, BEGIN(CC) AS CALC_DATE
FROM PERFTEST T01
WHERE START_DT BETWEEN DATE’1900-01-01′ AND DATE
EXPAND ON PERIOD(START_DT,DATE+1) AS CC BY INTERVAL ‘1’ DAY
ORDER BY PK,CALC_DATE

QueryBandTotalIOCountAMPCPUTimeSpoolUsageStartTimeFirstRespTime
EXPAND7084187,0011141,8234842068172813.09.2022 19:19:23.12000013.09.2022 19:19:41.360000

The optimized query requires 99% fewer IOs than the original query and 71% fewer CPU seconds. Spool usage has remained the same.

The case study showed how we could often achieve huge performance benefits in Teradata by rewriting queries. Often the same goal can be achieved much more efficiently by changing the SQL and without having “hints” available as they exist in Oracle.

  • Maybe that could be possible with XML_AGG as well?
    We usually (cross) join a calendar table to such a query which contains each date and the end of day or end of month date records.

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

    You might also like

    >