# The Teradata Recursive Query for Performance Tuning

May 2, 2023

## 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

The superiority of either query 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 efficiency is a crucial consideration, we suggest evaluating the resource utilization in Table DBC.DBQLOGTBL for both options and making a decision based on the results.