sql - 需要将递归 CTE 查询转换为索引友好查询

Question

在经历了编写递归 CTE 查询以满足我的需要的所有艰苦工作之后，我意识到我无法使用它，因为它在索引 View 中不起作用。所以我需要其他东西来替换下面的 CTE。 (是的，您可以在非索引 View 中使用 CTE，但这对我来说太慢了)。

要求:

1. 我的最终目标是拥有一个 self 更新的索引 View (它不一定是 View ，而是类似的东西)......也就是说，如果 View 加入的任何表中的数据发生变化，然后 View 需要更新自身。

2. View 需要索引，因为它必须非常快，并且数据不会经常更改。不幸的是，使用 CTE 的非索引 View 需要 3-5 秒才能运行，这对我的需要来说太长了。我需要查询以毫秒为单位运行。递归表里面有几十万条记录。

就我的研究而言，满足所有这些要求的最佳解决方案是索引 View ，但我对任何解决方案持开放态度。

CTE 可以在我的 other post 的答案中找到.或者这里又是:

DECLARE @tbl TABLE ( 
     Id INT 
    ,[Name] VARCHAR(20) 
    ,ParentId INT 
    ) 

INSERT INTO @tbl( Id, Name, ParentId ) 
VALUES 
 (1, 'Europe', NULL) 
,(2, 'Asia',   NULL) 
,(3, 'Germany', 1) 
,(4, 'UK',      1) 
,(5, 'China',   2) 
,(6, 'India',   2) 
,(7, 'Scotland', 4) 
,(8, 'Edinburgh', 7) 
,(9, 'Leith', 8) 

; 
DECLARE @tbl2 table (id int, abbreviation varchar(10), tbl_id int)
INSERT INTO @tbl2( Id, Abbreviation, tbl_id ) 
VALUES 
 (100, 'EU', 1) 
,(101, 'AS', 2) 
,(102, 'DE', 3) 
,(103, 'CN', 5)

;WITH abbr AS (
    SELECT a.*, isnull(b.abbreviation,'') abbreviation
    FROM @tbl a
    left join @tbl2 b on a.Id = b.tbl_id
), abcd AS ( 
          -- anchor 
        SELECT  id, [Name], ParentID,
                CAST(([Name]) AS VARCHAR(1000)) [Path],
                cast(abbreviation as varchar(max)) abbreviation
        FROM    abbr
        WHERE   ParentId IS NULL 
        UNION ALL
          --recursive member 
        SELECT  t.id, t.[Name], t.ParentID, 
                CAST((a.path + '/' + t.Name) AS VARCHAR(1000)) [Path],
                isnull(nullif(t.abbreviation,'')+',', '') + a.abbreviation
        FROM    abbr AS t 
                JOIN abcd AS a 
                  ON t.ParentId = a.id 
       )
SELECT *, [Path] + ':' + abbreviation
FROM abcd

Answer 1

在解决了索引 View 的所有障碍(自连接、cte、udf 访问数据等)之后，我建议将以下内容作为您的解决方案。

创建支持函数

基于从根开始的最大深度 4(总共 5 个)。或者使用 CTE

CREATE FUNCTION dbo.GetHierPath(@hier_id int) returns varchar(max)
WITH SCHEMABINDING
as
begin
return (
    select FullPath =
               isnull(H5.Name+'/','') + 
               isnull(H4.Name+'/','') +
               isnull(H3.Name+'/','') +
               isnull(H2.Name+'/','') +
               H1.Name
             +
               ':'
             +
               isnull(STUFF(
               isnull(','+A1.abbreviation,'') +
               isnull(','+A2.abbreviation,'') + 
               isnull(','+A3.abbreviation,'') +
               isnull(','+A4.abbreviation,'') +
               isnull(','+A5.abbreviation,''),1,1,''),'')
    from dbo.HIER H1
    left join dbo.ABBR A1 on A1.hier_id = H1.Id
    left join dbo.HIER H2 on H1.ParentId = H2.Id
    left join dbo.ABBR A2 on A2.hier_id = H2.Id
    left join dbo.HIER H3 on H2.ParentId = H3.Id
    left join dbo.ABBR A3 on A3.hier_id = H3.Id
    left join dbo.HIER H4 on H3.ParentId = H4.Id
    left join dbo.ABBR A4 on A4.hier_id = H4.Id
    left join dbo.HIER H5 on H4.ParentId = H5.Id
    left join dbo.ABBR A5 on A5.hier_id = H5.Id
    where H1.id = @hier_id)
end
GO

向表本身添加列

例如完整路径列，如果需要，通过在“:”上拆分 dbo.GetHierPath 的结果在 CTE 中添加其他 2 列 (left=>path, right=>abbreviations)

-- index maximum key length is 900, based on your data, 400 is enough
ALTER TABLE HIER ADD FullPath VARCHAR(400)

维护列

由于层次结构的性质，可以删除影响 Y 后代和 Z 祖先的记录 X，这在 INSTEAD OF 或 AFTER 触发器中都很难识别。所以替代方法是根据条件

• 如果 View 加入的任何表中的数据发生变化，则 View 需要自行更新。
• 使用 CTE 的非索引 View 需要 3-5 秒才能运行，这对我的需求来说太长了

我们只需再次遍历整个表来维护数据，每次更新需要 3-5 秒(如果 5 连接查询效果更好，则更快)。

CREATE TRIGGER TG_HIER
ON HIER
AFTER INSERT, UPDATE, DELETE
AS
UPDATE HIER
SET FullPath = dbo.GetHierPath(HIER.Id)

最后，在表本身上索引新列

create index ix_hier_fullpath on HIER(FullPath)

_{如果您打算通过 id 访问路径数据，那么它已经在表本身中而无需添加额外的索引。}

上面的TSQL引用了这些对象

修改表名和列名以适合您的模式。

CREATE TABLE dbo.HIER (Id INT Primary Key Clustered, [Name] VARCHAR(20) ,ParentId INT)
;
INSERT dbo.HIER( Id, Name, ParentId ) VALUES 
     (1, 'Europe', NULL) 
    ,(2, 'Asia',   NULL) 
    ,(3, 'Germany', 1) 
    ,(4, 'UK',      1) 
    ,(5, 'China',   2) 
    ,(6, 'India',   2) 
    ,(7, 'Scotland', 4) 
    ,(8, 'Edinburgh', 7) 
    ,(9, 'Leith', 8)
    ,(10, 'Antartica', NULL) 
; 
CREATE TABLE dbo.ABBR (id int primary key clustered, abbreviation varchar(10), hier_id int)
;
INSERT dbo.ABBR( Id, Abbreviation, hier_id ) VALUES 
     (100, 'EU', 1) 
    ,(101, 'AS', 2) 
    ,(102, 'DE', 3) 
    ,(103, 'CN', 5)
GO

编辑 - 可能更快的选择

鉴于每次都会重新计算所有记录，因此没有必要为单个 HIER.ID 返回 FullPath 的函数。 support function 中的查询可以在末尾没有 where H1.id = @hier_id 过滤器的情况下使用。此外，FullPath 的表达式可以很容易地从中间分解为 PathOnly 和 Abbreviation。或者只使用原来的 CTE，以速度更快者为准。