Teradata SQL Tuning: How Query Rewriting Can Reduce Runtime from 40 Minutes to Seconds

It's time to share a new Teradata SQL tuning case study that showcases the impressive impact of query rewriting on performance.

We are studying the query below that originally took 40 minutes to run.

As a SQL tuning specialist, I always prioritize adding missing statistics and refreshing stale ones.

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. Early subscribers keep launch access before the paid plan launches.

Get the next issue

I analyzed the SQL statement that generates a call detail report for a telecom company and selects the total minutes of use (MOU) for a specific day.

CALL_DATE partitions the call detail (CDR) table, and I expected partition elimination when using the CALL_DATE as a predicate of the WHERE condition.

I discovered an execution step that implemented partition elimination but was not executed optimally. It involved a product join with the large call detail table, which was improved by dynamic partition elimination.

Upon further analysis, I discovered that the Optimizer filtered call detail records at the end of the execution plan via the HAVING clause.

Moving the CALL_DATE filter to the driving table's WHERE condition enabled the Optimizer to apply static partition elimination at the start of the execution plan.

Get the next issue by email.

This minor adjustment decreased the query's execution time to mere seconds, while the initial query took 40 minutes to complete.

An authentic Teradata SQL Tuning Home Run

Conclusion:

Optimize data retrieval by applying filters as early as possible in the SQL statement, particularly on the driving table, to ensure efficient execution. Despite the optimizer's capabilities, it may not identify all potential opportunities.

Poorly performing query:

SELECTTOT.PHONE_ID, TOT.COUNTRY_CD, TOT.BILLING_ENGINE_CD, TOT.PRICEPLAN_GROUP_CD, TOT.RATING_CODE, TOT.CUSTOMER_ID, TOT.REPORTING_GROUP_CD1, TOT.ROAMING_IND, TOT.CORRECTIONS_IND, TOT.CALL_DATE, TOT.BILL_PERIOD_CD, 'A6' AS PROVIDER_CD, SUM(TOT.MINUTES_OF_USE) AS MINUTES_OF_USE, TOT.BILLED_IND, TOT.PREPAID_INDFROM(SELECTRG.PHONE_ID, RG.COUNTRY_CD, RG.BILLING_ENGINE_CD, RG.PRICEPLAN_GROUP_CD, RG.RATING_CODE, RG.CUSTOMER_ID, RG.REPORTING_GROUP_CD1, RG.ROAMING_IND, RG.CORRECTIONS_IND, RG.REPORTING_GROUP_CD2, RG.CALL_DATE, RG.BILL_PERIOD_CD, RG.MINUTES_OF_USE, RG.BILLED_IND, RG.PREPAID_INDFROM(SELECTCD.PHONE_ID, CD.COUNTRY_CD, CD.BILLING_ENGINE_CD, CD.PRICEPLAN_GROUP_CD, CD.RATING_CODE, CD.CUSTOMER_ID, CD.REPORTING_GROUP_CD1, CD.ROAMING_IND, CD.CORRECTIONS_IND, CD.REPORTING_GROUP_CD2, CD.CALL_DATE, CD.MINUTES_OF_USE, RG.BILL_PERIOD_CD, CD.BILLED_IND, CD.PREPAID_INDFROMCALL_DETAILS CDLEFT JOIN(SELECT BILL_PERIOD_CD, CALL_DATE FROM REPORTING_GROUP GROUP BY 1,2) RGON CD.CALL_DATE = RG.CALL_DATEWHEREBILLING_ENGINE_CD IN ('AMDOCS')AND BILLED_IND = 'N'AND REPORTING_GROUP_CD2 IS NOT NULL) RG

LEFT JOINREPORTING_GROUP R1ON RG.CALL_DATE = R1.CALL_DATEAND RG.REPORTING_GROUP_CD1 = R1.REPORTING_GROUP_CD1AND RG.BILL_PERIOD_CD = R1.BILL_PERIOD_CD

LEFT JOINREPORTING_GROUP R2ON RG.CALL_DATE = R2.CALL_DATEAND RG.REPORTING_GROUP_CD2 = R2.REPORTING_GROUP_CD1AND RG.BILL_PERIOD_CD = R2.BILL_PERIOD_CDWHERE NOT (R1.REPORTING_GROUP_CD1 IS NULL OR R2.REPORTING_GROUP_CD1 IS NULL)) TOTGROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,14,15HAVING TOT.CALL_DATE = DATE '2015-05-21';

This is the execution plan for the poorly-performing query:

1) First, we lock CALL_DB.REPORTING_GROUP for access,and we lock CALL_DB.CALL_DETAILS for access.2) Next, we do an all-AMPs SUM step to aggregate fromCALL_DB.REPORTING_GROUP by way of an all-rowsscan with no residual conditions , grouping by field1 (CALL_DB.REPORTING_GROUP.BILL_PERIOD_CD,CALL_DB.REPORTING_GROUP.CALL_DATE). AggregateIntermediate Results are computed globally, then placed in Spool 3.The size of Spool 3 is estimated with high confidence to be 12rows (612 bytes). The estimated time for this step is 0.02seconds.

3) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way ofan all-rows scan into Spool 1 (used to materialize view, derivedtable, or table function RG) (all_amps) (compressed columnsallowed), which is built locally on the AMPs. The size of Spool 1is estimated with high confidence to be 12 rows (444 bytes). Theestimated time for this step is 0.01 seconds.

4) We execute the following steps in parallel.1) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) byway of an all-rows scan into Spool 8 (all_amps) (compressedcolumns allowed), which is duplicated on all AMPs. Then wedo a SORT to order Spool 8 by the hash code of (CALL_DB.REPORTING_GROUP.BILL_PERIOD_CD,CALL_DB.REPORTING_GROUP.CALL_DATE). The sizeof Spool 8 is estimated with high confidence to be 1,080 rows(31,320 bytes). The estimated time for this step is 0.01seconds.

2) We do an all-AMPs RETRIEVE step fromCALL_DB.REPORTING_GROUP by way of anall-rows scan with no residual conditions locking for accessinto Spool 9 (all_amps) (compressed columns allowed), whichis built locally on the AMPs. Then we do a SORT to orderspool 9 by the hash code of (CALL_DB.REPORTING_GROUP.BILL_PERIOD_CD,CALL_DB.REPORTING_GROUP.CALL_DATE). The sizeof Spool 9 is estimated with high confidence to be 4,277 rows(149,695 bytes). The estimated time for this step is 0.01seconds.

5) We do an all-AMPs JOIN step from Spool 8 (Last Use) by way of aRowHash match scan, which is joined to Spool 9 (Last Use) by wayof a RowHash match scan. Spool 8 and Spool 9 are joined using amerge join, with a join condition of ("(CALL_DATE = CALL_DATE) AND(BILL_PERIOD_CD = BILL_PERIOD_CD)").  The resultgoes into Spool 10 (all_amps) (compressed columns allowed), whichis duplicated on all AMPs. Then we do a SORT to partition byrowkey. The size of Spool 10 is estimated with low confidence tobe 384,930 rows (19,631,430 bytes). The estimated time for thisstep is 0.03 seconds.

6) We execute the following steps in parallel.

1) We do an all-AMPs JOIN step from Spool 10 (Last Use) by wayof an all-rows scan, which is joined toCALL_DB.CALL_DETAILS with a condition of ("(CALL_DB.CALL_DETAILS.BILLED_IND = 'N') AND (CALL_DB.CALL_DETAILS.BILLING_ENGINE_CD = 'AMDOCS ')").

Spool 10 and CALL_DB.CALL_DETAILS are joinedusing a product join, with a join condition of ("(CALL_DB.CALL_DETAILS.CALL_DATE = CALL_DATE) AND((CALL_DB.CALL_DETAILS.CALL_DATE = CALL_DATE) AND(CALL_DB.CALL_DETAILS.REPORTING_GROUP_CD2 = REPORTING_GROUP_CD1))") enhanced by dynamic partition elimination.The input table CALL_DB.CALL_DETAILS will not be cached inmemory, but it is eligible for synchronized scanning.  Theresult goes into Spool 11 (all_amps) (compressed columnsallowed), which is built locally on the AMPs.  The size ofSpool 11 is estimated with low confidence to be 136,515 rows(21,159,825 bytes).  The estimated time for this step is 4.15seconds.

2) We do an all-AMPs RETRIEVE step fromCALL_DB.REPORTING_GROUP by way of anall-rows scan with no residual conditions locking for accessinto Spool 12 (all_amps) (compressed columns allowed), whichis duplicated on all AMPs. The size of Spool 12 is estimatedwith high confidence to be 384,930 rows (13,857,480 bytes).

The estimated time for this step is 0.02 seconds.

7) We do an all-AMPs JOIN step from Spool 11 (Last Use) by way of anall-rows scan, which is joined to Spool 12 (Last Use) by way of anall-rows scan. Spool 11 and Spool 12 are joined using a singlepartition hash join, with a join condition of ("(BILL_PERIOD_CD = BILL_PERIOD_CD) AND ((CALL_DATE= CALL_DATE) AND ((CALL_DATE = CALL_DATE) AND((BILL_PERIOD_CD = BILL_PERIOD_CD) AND ((CALL_DATE= CALL_DATE) AND (REPORTING_GROUP_CD1 = REPORTING_GROUP_CD1 )))))").  The result goes into Spool 7 (all_amps) (compressed columns allowed), which isbuilt locally on the AMPs.  The size of Spool 7 is estimated withlow confidence to be 136,515 rows (18,702,555 bytes).  Theestimated time for this step is 0.04 seconds.

8) We do an all-AMPs SUM step to aggregate from Spool 7 (Last Use) byway of an all-rows scan, grouping by field1 (CALL_DB.CALL_DETAILS.PHONE_ID, CALL_DB.CALL_DETAILS.COUNTRY_CD, CALL_DB.CALL_DETAILS.BILLING_ENGINE_CD, CALL_DB.CALL_DETAILS.PRICEPLAN_GROUP_CD, CALL_DB.CALL_DETAILS.RATING_CODE, CALL_DB.CALL_DETAILS.CUSTOMER_ID, CALL_DB.CALL_DETAILS.REPORTING_GROUP_CD1, CALL_DB.CALL_DETAILS.ROAMING_IND, CALL_DB.CALL_DETAILS.CORRECTIONS_IND, CALL_DB.CALL_DETAILS.CALL_DATE, CALL_DB.REPORTING_GROUP.BILL_PERIOD_CD,'A6' ,CALL_DB.CALL_DETAILS.BILLED_IND, CALL_DB.CALL_DETAILS.PREPAID_IND).  Aggregate

Intermediate Results are computed globally, then placed in SpoolThe size of Spool 13 is estimated with low confidence to be102,387 rows (31,637,583 bytes).  The estimated time for this stepis 0.17 seconds.9) We do an all-AMPs RETRIEVE step from Spool 13 (Last Use) by way ofan all-rows scan with a condition of ("CALL_DATE = DATE'2015-05-21'") into Spool 5 (group_amps), which is built locallyon the AMPs. The size of Spool 5 is estimated with low confidenceto be 102,387 rows (14,846,115 bytes). The estimated time forthis step is 0.02 seconds.

10) Finally, we send out an END TRANSACTION step to all AMPs involvedin processing the request.

-> The contents of Spool 5 are sent back to the user as the result ofstatement 1. The total estimated time is 4.44 seconds.

Here is the optimized query:

EXPLAIN

SELECTTOT.PHONE_ID, TOT.COUNTRY_CD, TOT.BILLING_ENGINE_CD, TOT.PRICEPLAN_GROUP_CD, TOT.RATING_CODE, TOT.CUSTOMER_ID, TOT.REPORTING_GROUP_CD1, TOT.ROAMING_IND, TOT.CORRECTIONS_IND, TOT.CALL_DATE, TOT.BILL_PERIOD_CD, 'A6' AS PROVIDER_CD, SUM(TOT.MINUTES_OF_USE) AS MINUTES_OF_USE, TOT.BILLED_IND, TOT.PREPAID_IND

FROM(SELECTRG.PHONE_ID, RG.COUNTRY_CD, RG.BILLING_ENGINE_CD, RG.PRICEPLAN_GROUP_CD, RG.RATING_CODE, RG.CUSTOMER_ID, RG.REPORTING_GROUP_CD1, RG.ROAMING_IND, RG.CORRECTIONS_IND, RG.REPORTING_GROUP_CD2, RG.CALL_DATE, RG.BILL_PERIOD_CD, RG.MINUTES_OF_USE, RG.BILLED_IND, RG.PREPAID_IND

FROM(SELECTCD.PHONE_ID, CD.COUNTRY_CD, CD.BILLING_ENGINE_CD, CD.PRICEPLAN_GROUP_CD, CD.RATING_CODE, CD.CUSTOMER_ID, CD.REPORTING_GROUP_CD1, CD.ROAMING_IND, CD.CORRECTIONS_IND, CD.REPORTING_GROUP_CD2, CD.CALL_DATE, CD.MINUTES_OF_USE, RG.BILL_PERIOD_CD, CD.BILLED_IND, CD.PREPAID_IND

FROMCALL_DETAILS CDLEFT JOIN(SELECT BILL_PERIOD_CD, CALL_DATE FROM REPORTING_GROUP GROUP BY 1,2) RGON CD.CALL_DATE = RG.CALL_DATEWHEREBILLING_ENGINE_CD IN ('AMDOCS')AND BILLED_IND = 'N'AND REPORTING_GROUP_CD2 IS NOT NULLAND CD.CALL_DATE = DATE '2015-05-21' -- The optimized place of the filter

) RG

LEFT JOINREPORTING_GROUP R1ON RG.CALL_DATE = R1.CALL_DATEAND RG.REPORTING_GROUP_CD1 = R1.REPORTING_GROUP_CD1AND RG.BILL_PERIOD_CD = R1.BILL_PERIOD_CDLEFT JOINREPORTING_GROUP R2ON RG.CALL_DATE = R2.CALL_DATEAND RG.REPORTING_GROUP_CD2 = R2.REPORTING_GROUP_CD1AND RG.BILL_PERIOD_CD = R2.BILL_PERIOD_CDWHERE NOT (R1.REPORTING_GROUP_CD1 IS NULL OR R2.REPORTING_GROUP_CD1 IS NULL)

) TOTGROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,14,15;

Below is the optimized query execution plan:

1) First, we lock CALL_DB.REPORTING_GROUP for access,and we lock CALL_DB.CALL_DETAILS for access.2) Next, we do an all-AMPs SUM step to aggregate from a singlepartition of CALL_DB.REPORTING_GROUP with acondition of ("CALL_DB.REPORTING_GROUP.CALL_DATE =DATE '2015-05-21'") with a residual condition of ("CALL_DB.REPORTING_GROUP.CALL_DATE = DATE'2015-05-21'") , grouping by field1 (CALL_DB.REPORTING_GROUP.BILL_PERIOD_CD,CALL_DB.REPORTING_GROUP.CALL_DATE). AggregateIntermediate Results are computed globally, then placed in Spool 3.

The size of Spool 3 is estimated with low confidence to be 4 rows(204 bytes). The estimated time for this step is 0.01 seconds.3) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way ofan all-rows scan into Spool 1 (used to materialize view, derivedtable or table function RG) (all_amps) (compressed columnsallowed), which is built locally on the AMPs. The size of Spool 1is estimated with low confidence to be 4 rows (148 bytes). Theestimated time for this step is 0.01 seconds.

4) We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) byway of an all-rows scan with a condition of ("RG.CALL_DATE =DATE '2015-05-21'") into Spool 8 (all_amps) (compressedcolumns allowed), which is duplicated on all AMPs. Then wedo a SORT to order Spool 8 by the hash code of (CALL_DB.REPORTING_GROUP.BILL_PERIOD_CD).

The size of Spool 8 is estimated with low confidence to be360 rows (10,440 bytes). The estimated time for this step is0.01 seconds.

2) We do an all-AMPs RETRIEVE step from a single partition ofCALL_DB.REPORTING_GROUP with a condition of("CALL_DB.REPORTING_GROUP.CALL_DATE = DATE'2015-05-21'") with a residual condition of ("CALL_DB.REPORTING_GROUP.CALL_DATE = DATE'2015-05-21'") locking for access into Spool 9 (all_amps)(compressed columns allowed), which is built locally on theAMPs.  Then we do a SORT to order Spool 9 by the hash code of(CALL_DB.REPORTING_GROUP.BILL_PERIOD_CD).

The size of Spool 9 is estimated with high confidence to be1,591 rows (55,685 bytes).  The estimated time for this stepis 0.00 seconds.

3) We do an all-AMPs RETRIEVE step from a single partition ofCALL_DB.REPORTING_GROUP with a condition of("CALL_DB.REPORTING_GROUP.CALL_DATE = DATE'2015-05-21'") with a residual condition of ("CALL_DB.REPORTING_GROUP.CALL_DATE = DATE'2015-05-21'") locking for access into Spool 10 (all_amps)(compressed columns allowed), which is duplicated on all AMPs.

The size of Spool 10 is estimated with high confidence to be143,190 rows (5,011,650 bytes).  The estimated time for thisstep is 0.01 seconds.

4) We do an all-AMPs RETRIEVE step from a single partition ofCALL_DB.CALL_DETAILS with a condition of ("CALL_DB.CALL_DETAILS.CALL_DATE = DATE'2015-05-21'") with a residual condition of ("(NOT(CALL_DB.CALL_DETAILS.REPORTING_GROUP_CD2 IS NULL ))AND ((CALL_DB.CALL_DETAILS.CALL_DATE = DATE'2015-05-21') AND ((CALL_DB.CALL_DETAILS.BILLED_IND = 'N') AND(CALL_DB.CALL_DETAILS.BILLING_ENGINE_CD = 'AMDOCS')))") into Spool 11 (all_amps) (compressed columns allowed),which is built locally on the AMPs.  The size of Spool 11 isestimated with low confidence to be 212,693 rows (27,224,704bytes).  The estimated time for this step is 0.06 seconds.

5) We do an all-AMPs JOIN step from Spool 8 (Last Use) by way of aRowHash match scan, which is joined to Spool 9 (Last Use) by wayof a RowHash match scan. Spool 8 and Spool 9 are joined using amerge join, with a join condition of ("(BILL_PERIOD_CD =BILL_PERIOD_CD) AND (CALL_DATE = CALL_DATE)").  The resultgoes into Spool 12 (all_amps) (compressed columns allowed), whichis duplicated on all AMPs. The size of Spool 12 is estimated withlow confidence to be 143,190 rows (7,302,690 bytes). Theestimated time for this step is 0.02 seconds.

6) We do an all-AMPs JOIN step from Spool 10 (Last Use) by way of anall-rows scan, which is joined to Spool 11 (Last Use) by way of anall-rows scan. Spool 10 and Spool 11 are joined using a singlepartition hash join, with a join condition of ("(REPORTING_GROUP_CD1 =REPORTING_GROUP_CD1) AND (CALL_DATE = CALL_DATE)").  The result goes intoSpool 13 (all_amps) (compressed columns allowed), which is builtlocally on the AMPs. The size of Spool 13 is estimated with lowconfidence to be 654,535 rows (94,907,575 bytes). The estimatedtime for this step is 0.11 seconds.

7) We do an all-AMPs JOIN step from Spool 12 (Last Use) by way of anall-rows scan, which is joined to Spool 13 (Last Use) by way of anall-rows scan. Spool 12 and Spool 13 are joined using a singlepartition hash join, with a join condition of ("(REPORTING_GROUP_CD2 =REPORTING_GROUP_CD1) AND ((CALL_DATE = CALL_DATE) AND ((CALL_DATE = CALL_DATE)AND ((BILL_PERIOD_CD = BILL_PERIOD_CD) AND((CALL_DATE = CALL_DATE) AND ((CALL_DATE = CALL_DATE) AND(BILL_PERIOD_CD = BILL_PERIOD_CD ))))))").  Theresult goes into Spool 7 (all_amps) (compressed columns allowed),which is built locally on the AMPs. The size of Spool 7 isestimated with low confidence to be 473,348 rows (64,848,676bytes). The estimated time for this step is 0.10 seconds.

8) We do an all-AMPs SUM step to aggregate from Spool 7 (Last Use) byway of an all-rows scan , grouping by field1 (CALL_DB.CALL_DETAILS.PHONE_ID,CALL_DB.CALL_DETAILS.COUNTRY_CD,CALL_DB.CALL_DETAILS.BILLING_ENGINE_CD,CALL_DB.CALL_DETAILS.PRICEPLAN_GROUP_CD,CALL_DB.CALL_DETAILS.RATING_CODE,CALL_DB.CALL_DETAILS.CUSTOMER_ID,CALL_DB.CALL_DETAILS.REPORTING_GROUP_CD1,CALL_DB.CALL_DETAILS.ROAMING_IND,CALL_DB.CALL_DETAILS.CORRECTIONS_IND,CALL_DB.CALL_DETAILS.CALL_DATE,CALL_DB.REPORTING_GROUP.BILL_PERIOD_CD,'TOT' ,CALL_DB.CALL_DETAILS.BILLED_IND,CALL_DB.CALL_DETAILS.PREPAID_IND).  AggregateIntermediate Results are computed globally, then placed in SpoolThe size of Spool 14 is estimated with no confidence to be355,011 rows (109,698,399 bytes).  The estimated time for thisstep is 0.32 seconds.

9) We do an all-AMPs RETRIEVE step from Spool 14 (Last Use) by way ofan all-rows scan into Spool 5 (group_amps), which is built locallyon the AMPs. The size of Spool 5 is estimated with no confidenceto be 355,011 rows (51,476,595 bytes). The estimated time forthis step is 0.06 seconds.

10) Finally, we send out an END TRANSACTION step to all AMPs involvedin processing the request.

-> The contents of Spool 5 are sent back to the user as the result ofstatement 1. The total estimated time is 0.70 seconds.

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 before the paid plan launches 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.