Início / dba / Perguntas / 96098
Accepted
Eric S
Asked: 2015-03-25 09:09:35 +0800 CST

Encontrando o final de uma cadeia de Relacionamento de forma otimizada

  • 772

Eu tenho uma tabela que contém uma coluna id, uma coluna pai e uma coluna filho. Então, para cada registro, eu sei qual registro vem antes dele e o que virá depois dele em um relacionamento em cadeia, mas do registro individual, não sei em que posição está dentro da cadeia nem qual é a primeira parte ou a última parte de a corrente.

Para fins de demonstração, suponha que temos a seguinte configuração. Percebo que há muitos problemas de design com esse tipo de configuração, mas é com isso que tenho que trabalhar.

CREATE TABLE Relationships (
ID VARCHAR(4), 
ParentID VARCHAR(4),
ChildID VARCHAR(4)
)

-- Insert root entries with their children
INSERT INTO Relationships (ID, ParentID, ChildID)
VALUES ('0001', '', '0003'), ('0002', '', '0004')

-- Now add further entries for each relationship chain
INSERT INTO Relationships(ID, ParentID, ChildID)
VALUES('0003', '0001', '0005'), ('0005', '0003', '0006'), ('0006', '0005', '0007'), ('0007', '0006', ''), 
  ('0004', '0002', '')

--Now we have two chains of 0001 -> 0003 -> 0005 -> 0006 -> 0007 and 0002 ->   0004

Com um CTE recursivo como abaixo, posso descobrir como todos os registros estão relacionados em sua cadeia e sua posição dentro da cadeia.

WITH RelationshipChain AS (
SELECT ID, ParentID, ChildID, 0 AS Seq, ID AS RootID
FROM Relationships WHERE ParentID = ''
UNION ALL
SELECT r2.ID, r2.ParentID, r2.ChildID, rc.Seq + 1 AS Seq, rc.RootID AS RootID
FROM Relationships r2 
INNER JOIN RelationshipChain rc ON rc.ChildID = r2.ID 
)

SELECT * FROM RelationshipChain
ORDER BY RootID, Seq

Para cerca de 2 milhões de registros, isso é executado em cerca de 30 segundos, o que é muito bom; no entanto, se eu tentar incluir também a parte final da cadeia, levará 4 vezes mais tempo para ser executado. Atualmente, estou fazendo assim:

WITH RelationshipChain AS (
SELECT ID, ParentID, ChildID, 0 AS Seq, ID AS RootID
FROM Relationships WHERE ParentID = ''
UNION ALL
SELECT r2.ID, r2.ParentID, r2.ChildID, rc.Seq + 1 AS Seq, rc.RootID AS RootID
FROM Relationships r2 
INNER JOIN RelationshipChain rc ON rc.ChildID = r2.ID 
)

SELECT * 
FROM RelationshipChain rc
CROSS APPLY (
SELECT MAX(Seq) AS FinalSeq FROM RelationshipChain WHERE rc.RootID = RootID
) AS b
CROSS APPLY (
SELECT ID AS LastChild FROM RelationshipChain WHERE b.FinalSeq = seq AND rc.RootID = RootID
) AS c
ORDER BY RootID, Seq

Existe uma maneira de fazer isso de forma mais eficiente?

sql-server performance

1 respostas

  1. Best Answer

    Não tenho seu conjunto de dados, então não posso testar se isso é melhor, mas parece melhor. Depois de construir as cadeias, nós as invertemos para encontrar o item 'mais filho' em cada cadeia e, em seguida, juntamos de volta à cadeia original.

    -- The same as your first CTE
;WITH RelationshipChain AS (
SELECT ID, ParentID, ChildID, 0 AS Seq, ID AS RootID
FROM Relationships WHERE ParentID = ''
UNION ALL
SELECT r2.ID, r2.ParentID, r2.ChildID, rc.Seq + 1 AS Seq, rc.RootID AS RootID
FROM Relationships r2 
INNER JOIN RelationshipChain rc ON rc.ChildID = r2.ID 
)

-- This CTE is new and reverses the sense of the chains
, BackChain AS (
SELECT ID, RootID, ROW_NUMBER() OVER (PARTITION BY RootID ORDER BY Seq DESC) BackSeq
FROM RelationshipChain
)

-- Now we join each chain to the childest item in the reverse chain
SELECT 
    rc.ID
    , rc.ParentID
    , rc.ChildID
    , rc.Seq
    , rc.RootID
    , bc.ID ChainEndID
FROM RelationshipChain rc INNER JOIN BackChain bc ON RC.RootID = bc.RootID AND bc.BackSeq = 1
ORDER BY RootID, Seq

    Resultados com seus dados de amostra:

    ID   ParentID ChildID Seq         RootID ChainEndID
---- -------- ------- ----------- ------ ----------
0001          0003    0           0001   0007
0003 0001     0005    1           0001   0007
0005 0003     0006    2           0001   0007
0006 0005     0007    3           0001   0007
0007 0006             4           0001   0007
0002          0004    0           0002   0004
0004 0002             1           0002   0004

    Os índices ajudarão no desempenho.

    • 2

relate perguntas