DWHPro Insights

Improving SQL Performance with Simple Query Rewrites: Dealing with Duplicates and Business Calendars

By Roland Wenzlofsky ·

Improving SQL Performance with Simple Query Rewrites: Dealing with Duplicates and Business Calendars

The Teradata flavor of SQL is still, in principle, a declarative language. Hence, there can be multiple ways to describe an SQL query and achieve the same result. While the answer is the same, Teradata may use a completely different execution plan based on how the query is expressed. One approach is investing in heavy COLLECT STATISTICS to reduce resource consumption. However, many times a simple rewrite of the query can achieve enormous performance improvements. In this article, we discuss two such cases revolving around duplicates and calendars.

Show duplicate records

Sooner or later, we will face the case that a table or a query produces duplicate values. While the root causes are beyond the scope here, more often than not, we need to examine these records and see what could be wrong. A common approach is using a GROUP BY construct:

SELECT b.*FROM badTable bWHERE (col1, col2, col3, ...) IN (SELECT col1, col2, col3, ...FROM badTable cGROUP BY col1, col2, col3, ...HAVING count(*) > 1)

This approach requires parsing the table twice, once to count occurrences and another one to return the matching data. From an expressiveness point of view, it is not directly evident what the query intention is. Contrast with the following approach:

SELECT b.*FROM badTable bQUALIFY count(*) OVER (PARTITION BY col1, col2, col3, ...) > 1 

QUALIFY expresses duplicate grouping directly, but its cost depends on the plan. It groups NULL keys, whereas tuple IN with NULL can exclude them: the audit returned two NULL-key rows for QUALIFY and zero for tuple IN. These are equivalent only under appropriate non-NULL constraints or an explicit NULL policy. Replace the ellipses in these templates with real key columns.

Business calendar dates

Many companies use a so-called “business calendar” instead of the Gregorian one, marking specific dates as working or non-working (e.g. because the company observes a national or religious holiday). Then, concepts like “last working day of a month” or “first working day of next month” deviate from one of the conventional calendars. Assume the question is formulated as “starting with a given (reference) date, report the next working day after it“. One approach we have seen goes like this:


SELECT b.CAL_DT, Min(n.CAL_DT) AS NEXT_WORKING_DTFROM businessCalendar bCROSS JOIN businessCalendar nWHERE n.IS_WORKING_DAY = 'Y'AND n.CAL_DT > b.CAL_DTGROUP BY b.CAL_DT

This approach involves a product join and a grouping instead of accessing the same table twice. Can we do better? Yes! We can just let Teradata handle all this logic internally:

SELECT b.CAL_DT,
 LEAD(CASE WHEN b.IS_WORKING_DAY = 'Y' THEN b.CAL_DT END IGNORE NULLS)
 OVER (ORDER BY b.CAL_DT) AS NEXT_WORKING_DT
FROM businessCalendar AS b
QUALIFY NEXT_WORKING_DT IS NOT NULL;

The window rewrite assumes exactly one row per CAL_DT with a consistent working-day flag. QUALIFY removes rows without a later working date, matching the inner-join query’s omission of the final dates. Without that filter the audited window fixture returned four rows instead of three. Resolve duplicate calendar dates first. Performance and spool estimates depend on data and the actual plan; no universal improvement follows from the syntax.

Conclusion

QUALIFY offers an additional query processing layer, which often goes unexplored. Still, it is not a panacea for all problems. The execution plan and available data demographics should always be checked to avoid unpleasant No more spool errors.

The way the question is posed can lead to SQL queries that are unnecessarily complex and result in inefficient execution plans. Rather than investing effort to tune a sub-optimal query, it is worth taking a step back and examining how the original question can be reformulated. Maybe the SQL can be rewritten more cleanly and compactly, saving us a ton.

Do you have an example to share? We’ll be glad to read your ideas and comments below!

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Practical data engineering insights, regularly

Join more than 3,500 data professionals: a regular newsletter with practical insights on data engineering, new data platforms (Snowflake, Databricks, Teradata) and AI.

Follow DWHPro in Google to see our articles more often in Search.

1 thought on “Improving SQL Performance with Simple Query Rewrites: Dealing with Duplicates and Business Calendars”

Leave a Comment

Get more practical data engineering advice

Join the DWHPro newsletter for practical data engineering tips, troubleshooting examples and data platform insights.

Protected by Security by CleanTalk and CleanTalk Anti-Spam