5 Tips for Optimizing Teradata SQL Queries: Avoiding Joins, Functions, Union, and Group By

1. Avoid multiple Joins to the same table

Reducing the number of joins to a table is crucial, as each additional join could lead to a full table scan or index access. This can be accomplished by implementing subqueries, volatile tables, or ordered analytical functions.

This example showcases applying an ordered analytical function to attain this objective.

SELECT
	A.SUBSCRIBER_ID,
 	B.VALUE AS INCOMING,
 	C.VALUE AS OUTGOING
FROM 
	SUBSCRIBERS A
	LEFT JOIN  CALLS B ON A.SUBSCRIBER_ID = B.SUBSCRIBER_ID AND B.INOUT_CD  = 'IN'
	LEFT JOIN  CALLS C ON A.SUBSCRIBER_ID = C.SUBSCRIBER_ID AND C.INOUT_CD  = 'OUT'
WHERE
	B.CALL_DATE BETWEEN DATE '2019-01-01' AND DATE '2019-12-31'
	C.CALL_DATE BETWEEN DATE '2019-01-01' AND DATE '2019-12-31'

SELECT
	A.SUBSCRIBER_ID,
 	B.INCOMING,
 	B.OUTGOING
FROM 
	SUBSCRIBERS A
	LEFT JOIN  
	(
		SELECT 
			SUBSCRIBER_ID,
			MAX(CASE WHEN INOUT_CD  = 'IN' THEN VALUE END) AS AVN_OFF_BAL,
			MAX(CASE WHEN INOUT_CD  = 'OUT' THEN VALUE END) AS BAL_OFF_BAL
		FROM
			CALLS 
		WHERE
			CALL_DATE BETWEEN DATE '2019-01-01' AND DATE '2019-12-31'
			AND INOUT_CD IN ('IN', 'OUT')
		GROUP BY 1
	) B
ON A.SUBSCRIBER_ID = B.SUBSCRIBER_ID;

2. Avoid Functions in Equi-Join Conditions

When applied to join columns, Teradata SQL functions do not typically allow direct ROWHASH access. Functions are commonly utilized for joining when the data model is insufficiently normalized. Therefore, it is crucial to prioritize improving the data model before considering using functions.

a.SUBSCRIBER_ID = TRIM(LEADING '0' FROM b.SUBSCRIBER_ID)

3. Avoid the Usage of UNION

Teradata performs deduplication in the spool space as UNION disallows row duplicates. To accomplish deduplication, all branches are duplicated into a shared spool space and sorted, allowing duplicates to be removed. This method is significantly more intricate than utilizing a UNION ALL statement. Furthermore, it’s noteworthy that UNION doesn’t support any of the newer tuning features, like pushing joins into individual branches. If you wish to learn more about this topic, you’re in the right place.

Use UNION ALL instead of UNION when duplicate values are acceptable (as demonstrated below):

Instead of: 

SELECT   CAST ('FIRST'  AS VARCHAR (15)), PK FROM TheTable
UNION 
SELECT   CAST ('SECOND'  AS VARCHAR (15)), PK FROM TheTable;

Use:

SELECT   CAST ('FIRST'  AS VARCHAR (15)), PK FROM TheTable
UNION ALL
SELECT   CAST ('SECOND'  AS VARCHAR (15)), PK FROM TheTable;

4. Avoid GROUP BY over Expressions

Aggregating expressions and Teradata SQL functions are not advisable, as rows must be distributed based on the ROWHASH to perform aggregation, similar to joins. The example below illustrates improved performance by directly aggregating the underlying columns instead of concatenating:

SELECT  COALESCE (SUBSCRIBER_ID ,0) || COALESCE(DESCRIPTION ,'') FROM THETABLE 
GROUP BY 
COALESCE (SUBSCRIBER_ID,0) || COALESCE(DESCRIPTION ,'');

Change to:

SELECT  COALESCE (SUBSCRIBER_ID ,0) || COALESCE(DESCRIPTION ,'') FROM THETABLE 
GROUP BY 
COALESCE (SUBSCRIBER_ID,0) , COALESCE(DESCRIPTION ,'');

5. Divide large SQL Queries into smaller ones

Consider modularizing your program logic to mitigate the risks of extensive SQL statements. As statements increase, they may overextend spool space limits and generate non-optimal execution plans due to the exponential growth of possible joins. Effective points for breaking up SQL statements include SET operators (e.g. UNION, UNION ALL), subqueries, and joins. Evaluate which subqueries can be incorporated into volatile tables, which offer the benefit of collecting statistics and preprocessing data, such as resolving functions to reduce the need for them in join columns.

6. Consider the Creation of additional Statistics and Indexes

Before delving into further optimizations, it is crucial to ascertain that the statistics are readily available and current. The optimizer should fashion an optimal plan founded on the statistics before delving into other optimization approaches.

Note that dynamic AMP sampling can often generate a sound execution plan without the need for collected statistics.

To swiftly obtain an overview of your statistics, execute the command DIAGNOSTIC HELPSTATS ON FOR SESSION before explaining your Teradata SQL. Every EXPLAIN statement executed during the session will list recommended statistics and their corresponding confidence level. While it may not be necessary to implement every suggestion, they can aid in identifying noteworthy inaccuracies in your statistics.

It is recommended to gradually include statistics and observe the alterations in the execution plan.

Consider using sample statistics instead of optimizer-suggested statistics for highly distinct columns.

COLLECT STATISTICS USING SAMPLE ON COLUMN aPrettyDistinctColumn;

Collecting basic statistics is less resource-intensive and often sufficient in such instances.

To verify outdated statistics, execute a SELECT * on the table and compare the outcome with the projected estimate in the Execution Plan.

To gain a comprehensive understanding of a table’s statistics, use the following command:

SHOW STATISTICS VALUES ON aTable;

If you have arrived here, you may also want to read our article on Performance Tuning:

Find more details about Teradata and SQL here:

https://support.teradata.com