Encontrando pai de nível superior de cada linha de uma tabela [SQL Server 2008]
Tenho seguindo duas tabelas
Pessoa da tabela
Id Name
1 A
2 B
3 C
4 D
5 E
Tabela RelaçãoHierarquia
ParentId CHildId
2 1
3 2
4 3
Isso formará uma estrutura semelhante a uma árvore
D
|
C
|
B
|
A
ParentId e ChildId são chaves estrangeiras da coluna Id da tabela Person
Eu preciso escrever SQL que pode me buscar de nível superior pai i-e raiz de cada pessoa.
Seguir CTE pode fazer isso para cada um. Eu converti isso para uma função e executei para cada linha de pessoa. Eu tenho cerca de 3k linhas na tabela Person e demora cerca de 10 segundos para fazer isso. Alguém pode sugerir uma abordagem que pode levar menos. O problema é a função que é executada após a execução do CTE 3k vezes
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