Mam następujące dwie tabele

Osoba stołowa

Id   Name
   1    A
   2    B
   3    C
   4    D
   5    E

Hierarchia Relacji Tabeli

ParentId   CHildId
   2         1
   3         2
   4         3

Spowoduje to utworzenie struktury podobnej do drzewa

      D
      |
      C
      |
      B
      |
      A

ParentId i ChildId to klucze obce kolumny Id tabeli osób

Muszę napisać SQL, który może pobrać mnie Rodzicem najwyższego poziomu i-e Root każdej osoby.

Podążanie za CTE może zrobić to dla każdego. Przekształciłem go w funkcję i uruchomiłem dla każdego wiersza osoby. Mam około 3k wierszy w tabeli osób i zajmuje to około 10 sekund. Czy ktoś może zaproponować podejście, które może zająć mniej. Problem polega na tym, że funkcja działająca po CTE działa 3k razy

DECLARE @childID INT 
SET @childID  = 1 --chield to search

;WITH RCTE AS
(
SELECT *, 1 AS Lvl FROM RelationHierarchy 
WHERE ChildID = @childID

UNION ALL

SELECT rh.*, Lvl+1 AS Lvl FROM dbo.RelationHierarchy rh
INNER JOIN RCTE rc ON rh.CHildId = rc.ParentId
 )
SELECT TOP 1 id, Name
FROM RCTE r
inner JOIN dbo.Person p ON p.id = r.ParentId
ORDER BY lvl DESC