Teradata Comparison Operators and Performance: Why Choosing the Right Operator Matters
Learn how Teradata Comparison Operators and Performance are linked, and how choosing the right operator can impact query performance. Column statistics play a crucial role in the Teradata Optimizer, and this article explores how to use them effectively.
The performance of Teradata is somewhat dependent on its comparison operators.
Selecting the appropriate comparison operator is crucial in distinguishing between efficient and inefficient queries.
How do the chosen comparison operators affect performance?
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.
Quality statistics are a crucial input for the Teradata Optimizer, significantly influencing the execution plan's aggressiveness or conservatism.
Get the next issue by email.
Teradata stores column-level statistics, which can be used for substrings of a column despite the inability to collect statistics at a finer granularity. Teradata 14.10 offers expanded capabilities for utilizing available statistics compared to previous versions.
Consider a SQL query featuring a WHERE clause targeting a designated column. Supposing a code is stored in a character column titled "ORIGIN_CD," our objective is to gather all entries where the column begins with the letter 'A.'
There are various ways to execute this SQL statement, and each option may yield a unique execution plan with distinct access paths, runtimes, and resource usage patterns.
SELECT * FROM <TABLE> WHERE ORIGIN_CD LIKE 'A%';
SELECT * FROM <TABLE> WHERE SUBSTR(ORIGIN_CD,1,1) = 'A';
In both cases, applying a function to the "ORIGIN_CD" column may hinder the Optimizer from utilizing column statistics, depending on your Teradata version. Avoiding functions on columns present in a WHERE or JOIN condition is recommended. Using functions on join columns could indicate a poor design or an outdated data model.
Conclusion:
Explore alternative solutions to enhance performance when applying functions to WHERE and JOIN conditions.
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.