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.