Teradata Tuning with PERIOD Data Type

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:

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;

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

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.