简化SQL Server查询

Question

以下查询需要大量时间返回两个不同数据库中的结果。有什么方法可以简化此查询？

WITH tblParent AS 
(
    SELECT * 
    FROM REFERENCES 
    WHERE referenced_id = 208593
    UNION ALL
    SELECT REFERENCES.* 
    FROM REFERENCES
    JOIN tblParent ON REFERENCES.referenced_id = tblParent.entity_Id
)
SELECT DISTINCT(entity_Id)
FROM tblParent
WHERE entity_Id <> 208593 AND field_type = 'ChildField'
OPTION(MAXRECURSION 5)

Answer 1

这应该简化它：

WITH tblParent AS 
(
    SELECT entity_Id, 0 c
    FROM [REFERENCES]
    WHERE referenced_id = 208593
    UNION ALL
    SELECT r.entity_Id, 1
    FROM [REFERENCES] r
    JOIN tblParent 
    ON r.referenced_id = tblParent.entity_Id
)
SELECT DISTINCT entity_Id
FROM tblParent
WHERE c = 1
OPTION(MAXRECURSION 5)

通过检查C值，很明显它是一个子值。我假设此文本为所有孩子都有field_type ='Childfield'。

(引用是一个保留的单词，而独特的不是函数(

Answer 2

由于您仅对Child EntityID感兴趣，然后只需在递归CTE中选择您需要的字段即可。

WITH tblParent AS (
    SELECT entity_Id, referenced_id as baseId
    FROM [REFERENCES]
    WHERE referenced_id = 208593
    UNION ALL
    SELECT t.entity_Id, cte.baseId
    FROM tblParent cte
    JOIN [REFERENCES] t 
      ON (t.referenced_id = cte.entity_Id 
          AND t.entity_Id <> cte.baseId -- to avoid a circular reference
          )
    WHERE t.field_type = 'ChildField'
)
SELECT DISTINCT entity_Id
FROM tblParent
WHERE entity_Id <> baseId 
OPTION(MAXRECURSION 5)

，如果Referend_id上有索引，则可能需要仔细检查。