如何加快我的SQL查询的执行，为超过458,000条记录生成类别面包屑？

Question

我搞乱了嵌套集层次结构模型，并且有一个名为CategoryNest的类别表，其中包含字段CategoryID int identity，CategoryName nvarchar（100），L int，R int，CreatedOn datetime，UpdatedOn datetime。

Note: "Store" is a schema in MS SQL Server. 

    SELECT CategoryID, 
      node AS CategoryName, 
      (
      SELECT 
        '/' + CAST(parent.CategoryID AS nvarchar(max)) 
      FROM 
        Store.CategoryNest node, 
        Store.CategoryNest parent 
      WHERE 
        node.L BETWEEN parent.L AND parent.R 
        AND node.CategoryID = Category.CategoryID 
      FOR XML PATH('') 
      ) AS BreadcrumbIDs 
    FROM 
    /* EDIT: this was an unnecessary join. 
      Removing it, helped me get results must faster. 
      (
      SELECT 
        node.CategoryID, 
        node.CategoryName node, 
        parent.CategoryName parent 
       FROM 
        Store.CategoryNest node, 
        Store.CategoryNest parent 
       WHERE 
        node.L BETWEEN parent.L AND parent.R 
     ) AS Category 
    */ 
      Store.CategoryNest Category 
    GROUP BY 
      node, 
      CategoryID 

查询工作正常，它产生所需的输出，但是当与458,000记录测试，它需要一个多小时（注：查询执行被取消）。

编辑：应用我编辑后的查询后，结果必须更快，但我仍然打开对进一步加快事情的建议。

Sample results: 
    CategoryID CategoryName Breadcrumbs 
    1   ROOT   /1 
    2   A   /1/2 
    3   AA   /1/2/3 
    4   AAA   /1/2/3/4 
    5   B   /1/5 
    6   C   /1/6 
    7   CC   /1/6/7 

我想知道是否有一种方法来优化此查询以在几秒钟内得到结果而不是几小时。

编辑：没有更多的小时，照顾了这一点，但仍然打开加速东西的建议。

谢谢。

Answer 1

使用子查询可以强制优化器使用循环连接。这是非常不幸的表现。

将子查询（... '/' + ...）重新规划为JOIN。

这部分是优化器，部分是不可优化的语义差异。