Optimizing Teradata Queries: A Case Study on Generating Rows Between Start and End Dates

This article presents a case study from my previous projects. Specifically, it showcases an implementation that generates rows for all days between a start date and an end date based on a primary key. To illustrate the issue, I have simplified the query. The following example serves as a demonstration:

Allow me to acquaint you with the implementation I optimized. Initially, the problem was resolved using a recursive query, which performed favorably. However, as the data expanded significantly, the query’s runtime escalated, substantially increasing.

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 improved query necessitates 99% fewer IO operations than the initial query and 71% fewer CPU seconds. Spool utilization has remained constant.

The case study highlighted Teradata’s potential for significant performance improvements through query optimization. Altering the SQL itself can often yield greater efficiency without using Oracle’s “hints.”