sql-server - 如何从具有引用主键列的列的大表(12k 记录)中有效地获取有序的层次列表

Question

我正在努力优化 SQL 查询并寻求帮助。 SQL Server 2008 的 T-SQL。

我有一组 Agents，它们有列 Id 和 ManagerId。经理只是一个代理，所以 ManagerId 就像一个外键回到同一个表。我正在编写一个查询，以根据管理层次结构将代理返回到有序列表中。

给定集合

Id  Name    ManagerId
-----------------------
1   Charlie 4
2   Alpha   NULL
3   Echo    5
4   Bravo   2
5   Delta   1
6   Foxtrot 3
7   Golf    6
8   Hotel   7
9   Juliet  8
10  India   8

我想按以下顺序返回值:

Id  Name    ManagerId
2   Alpha   NULL
4   Bravo   2
1   Charlie 4
5   Delta   1
3   Echo    5
6   Foxtrot 3
7   Golf    6
8   Hotel   7
9   Juliet  8
10  India   8

我现在使用的策略对 10 个值非常有效。我将使用它的真实场景大约是 12,000。当我在 10,000 个测试集上使用以下查询时，在我的笔记本电脑上永远需要 20 分钟。我正在使用带子查询的循环，所以我知道一定有更好的方法。

CREATE TABLE #hierarchy (rowNumber INT, agentId INT);
CREATE TABLE #finishedManagers (id INT);

DECLARE @index INT = 1;
DECLARE @count INT = (SELECT COUNT(Id) FROM agents);
DECLARE @thisId INT;

WHILE (@index <= @count)

BEGIN
    SET @thisId = ( 
        SELECT TOP 1
            a.Id
        FROM    
            agents a
        WHERE
            a.Id NOT IN (SELECT * FROM #finishedManagers)
            AND
            (a.ManagerId IS NULL OR a.ManagerId IN (SELECT agentId FROM #heirarchy))
            );

    INSERT INTO #hierarchy (rowNumber, agentId)
        SELECT
            @index,
            @thisId

    SET @index = @index + 1;

    INSERT INTO #finishedManagers(id)
        SELECT
            @thisId 
END
GO

SELECT 
    a.*
FROM 
    #hierarchy h
LEFT JOIN
    agents a ON h.agentId = a.Id
ORDER BY
    h.rowNumber;

DROP TABLE #hierarchy;
DROP TABLE #finishedManagers;

你会怎么做？

Answer 1

首先，尽可能避免循环。

以下是将递归 CTE 与 HIERARCHY 数据类型结合使用的示例。

递归 CTE 很棒，值得您花时间熟悉它们。但是，性能可能会因更大/更深的层次结构而受到影响。

还有其他使用 TEMP 表的技术，性能更高，但涉及更多。

示例

Declare @Top   int  = null  --<<  Sets top of Hier Just for FUN Try 3

;with cteP as (
      Select ID
            ,ManagerID 
            ,Name 
            ,HierID = convert(hierarchyid,'/'+convert(varchar(25),ID)+'/')
      From   YourTable 
      Where  IsNull(@Top,-1) = case when @Top is null then isnull(ManagerID ,-1) else ID end
      Union  All
      Select ID  = r.ID
            ,ManagerID  = r.ManagerID 
            ,Name   = r.Name
            ,HierID = convert(hierarchyid,p.HierID.ToString()+convert(varchar(25),r.ID)+'/')
      From   YourTable r
      Join   cteP p on r.ManagerID  = p.ID)
Select Lvl   = HierID.GetLevel()
      ,ID
      ,Name  
      ,ManagerID
 From cteP A
 Order By A.HierID

返回

Lvl ID  Name    ManagerID
1   2   Alpha   NULL
2   4   Bravo   2
3   1   Charlie 4
4   5   Delta   1
5   3   Echo    5
6   6   Foxtrot 3
7   7   Golf    6
8   8   Hotel   7
9   9   Juliet  8
9   10  India   8

EDIT - Temp Table Approach 25,000 row in 2 seconds

请注意，我的最大深度为 30 个级别。

Declare @Top int =null 
Select *
      ,Lvl=1
      ,HierID = convert(hierarchyid,'/'+convert(varchar(25),ID)+'/')
 Into #TempBld 
 From YourTable
 Where  IsNull(@Top,-1) = case when @Top is null then isnull(ManagerID,-1) else ID end

Declare @Cnt int=1
While @Cnt<=30
    Begin
        Insert Into #TempBld 
        Select A.*
              ,Lvl=B.Lvl+1
              ,HierID = convert(hierarchyid,b.HierID.ToString()+convert(varchar(25),a.ID)+'/')
         From  YourTable A
         Join  #TempBld B on (B.Lvl=@Cnt and A.ManagerID=B.ID)
        Set @Cnt=@Cnt+1
    End

Select Lvl
      ,ID
      ,Name
      ,ManagerID
 From #TempBld
 Order by HierID