Optimizing Teradata Queries: A Case Study on Generating Rows Between Start and End Dates
Learn how to optimize SQL queries in Teradata with this case study. See how a recursive query was replaced with a more efficient approach.
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:
| Primary Key | Start Date | End Date |
| 1 | 2022-09-12 | 2022-09-15 |
| Primary Key | Start Date | End Date | Calculated Date |
| 1 | 2022-09-12 | 2022-09-15 | 2022-09-12 |
| 1 | 2022-09-12 | 2022-09-15 | 2022-09-13 |
| 1 | 2022-09-12 | 2022-09-15 | 2022-09-14 |
| 1 | 2022-09-12 | 2022-09-15 | 2022-09-15 |
Here is the implementation I optimized. Initially, the problem was resolved using a recursive query, which performed well. However, as the data expanded significantly, the query's runtime increased substantially.
| QueryBand | TotalIOCount | AMPCPUTime | SpoolUsage | StartTime | FirstRespTime |
| RECURSION | 700180812,00 | 38859,82 | 348420681728 | 13.09.2022 19:11:11.830000 | 13.09.2022 19:17:28.820000 |
Below you can see the optimized query:
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. DWHPro Letters is free. Subscribe to get new issues by email.
| QueryBand | TotalIOCount | AMPCPUTime | SpoolUsage | StartTime | FirstRespTime |
| EXPAND | 7084187,00 | 11141,82 | 348420681728 | 13.09.2022 19:19:23.120000 | 13.09.2022 19:19:41.360000 |
The improved query requires 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. Rewriting the SQL itself can often yield greater efficiency without relying on optimizer hints.
Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe for free and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.