The Teradata Recursive Query for Performance Tuning

Problem-solving without a Teradata Recursive Query

To depict a corporate hierarchy in our instance, we can employ a non-recursive approach, illustrated by the query presented below:

SELECT
   -- Concatenation of all Companies
   CASE WHEN Level >= 3
   THEN TRIM(t03.Company_Id) || '.' ELSE '' END
   || CASE WHEN Level >= 2
   THEN TRIM(t02.Company_Id) || '.' ELSE '' END
   || TRIM(t01.Company_Id) AS Company_Hierarchy
   -- Level Calculation
  ,CASE NULL
     WHEN t01.Parent_Company_Id THEN 1
     WHEN t02.Parent_Company_Id THEN 2
     WHEN t03.Parent_Company_Id THEN 3
   END AS Level
FROM
   DWHPro.Company t01
LEFT OUTER JOIN
   DWHPro.Company B ON t01.Parent_Company_Id = t02.Company_Id
LEFT OUTER JOIN
   DWHPro.Company t03 ON t02.Parent_Company_Id = t03.Company_Id
ORDER BY 1
;

The above query has several harmful properties:

• We must know the number of hierarchy levels.
  If too few levels are considered, the query returns incorrect results. If too many hierarchy levels are considered, unnecessary joins are caused.
• If the number of hierarchy levels changes, the following changes to the SQL statement are necessary:
  - Joins need to be added or removed
  - One CASE Statement has to be adjusted to determine the levels
  - One CASE Statement has to be adjusted to calculate the Hierarchy

Problem-solving with a Teradata Recursive Query

WITH RECURSIVE Hierarchy
(Company_Hierarchy, Company_Id, Level) AS
(
--
SELECT
  TRIM(Company_Id) (VARCHAR(1024)) AS Company_Hierarchy
, Company_Id
, 1 AS Level
FROM DWHPro.Org
WHERE Parent_Company_Id IS NULL

UNION ALL

SELECT  t02.Company_Hierarchy || '.' || TRIM(t01.Company_Id) AS Company_Hierarchy
, t01.Company_Id
, t02.Level + 1 AS Level
FROM
   DWHPro.Org t01
  ,Hierarchy t02
WHERE t01.Parent_Company_Id = t02.Company_Id
AND Level <= 10 -- limit to avoid infinite loop errors
)
SELECT Company_Hierarchy, Company_Id, Level
FROM Hierarchy
ORDER BY 1
;

The above recursive query has several benefits:

• Only the required number of recursive join steps are executed
• We can limit the number of levels to avoid "runaway" recursions

The only modification required to enlarge the Company_Hierarchy column (previously defined as VARCHAR(1024)) is to adjust its size.

Performance Considerations

Which query performs better cannot be determined universally; it varies based on the characteristics of the data. We generally advise opting for the recursive option as it is more adaptable. If performance is a crucial consideration, we suggest evaluating the resource utilization in the DBC.DBQLOGTBL table for both options and making a decision based on the results.

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.

Get the next issue

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.