简化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

由于您只对子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) 

，你可能想仔细检查，如果有上referenced_id的索引。

Answer 2

这应该简化它：

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'。

（参考文献是保留字，DISTINCT不是一个函数）