是否可以强制SQL Server使用我想要优化递归cte查询的计划

Question

我在一个视图中对一棵大树使用递归cte进行了查询，当使用硬编码数字而不是参数进行查询时，该查询效果良好。是否可以强制SQL Server使用我想要优化此递归cte查询的计划？任何想法都将不胜感激。

这是具有递归CTE的视图-它检索给定实体下的所有节点：

CREATE VIEW adams_test_view AS
WITH eq_mi_cte(miId, eqId, miName, miCode) AS
    (SELECT ent.id, ent.id, ent.name, ent.code
    FROM entity ent
    UNION ALL
    SELECT e.id, eq_mi_cte.eqid, e.name, e.code
    FROM entity e
    INNER JOIN eq_mi_cte ON e.pid = eq_mi_cte.miid)
SELECT * FROM eq_mi_cte

对带有参数的视图的查询似乎是查询整个视图，然后过滤它，因为树太大，所以永远不会结束——我们得到一个最大递归错误：

DECLARE @TopLevelEnt int
SET @TopLevelEnt = 187317;
select * from adams_test_view
WHERE eqId = @TopLevelEnt

以下是计划（我对索引名称的称呼）：

  |--Filter(WHERE:([Recr1009]=[@TopLevelEnt]))
       |--Index Spool(WITH STACK)
            |--Concatenation
                 |--Compute Scalar(DEFINE:([Expr1012]=(0)))
                 |    |--Index Scan(OBJECT:([local_dbname].[dbo].[Entity].[EntityParentId] AS [ent]))
                 |--Assert(WHERE:(CASE WHEN [Expr1014]>(100) THEN (0) ELSE NULL END))
                      |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1014], [Recr1004], [Recr1005], [Recr1006], [Recr1007]))
                           |--Compute Scalar(DEFINE:([Expr1014]=[Expr1013]+(1)))
                           |    |--Table Spool(WITH STACK)
                           |--Index Seek(OBJECT:([local_dbname].[dbo].[Entity].[EntityParentId] AS [e]), SEEK:([e].[PId]=[Recr1004]) ORDERED FORWARD)

现在，当我使用硬编码值执行相同的查询时，它会返回fine:

查询：

SELECT * FROM adams_test_view
WHERE eqId = 187317

计划：

  |--Index Spool(WITH STACK)
       |--Concatenation
            |--Compute Scalar(DEFINE:([Expr1012]=(0)))
            |    |--Clustered Index Seek(OBJECT:([local_dbname].[dbo].[Entity].[PK__Entity__2E1BDC42] AS [ent]), SEEK:([ent].[Id]=(187317)) ORDERED FORWARD)
            |--Assert(WHERE:(CASE WHEN [Expr1014]>(100) THEN (0) ELSE NULL END))
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1014], [Recr1004], [Recr1005], [Recr1006], [Recr1007]))
                      |--Compute Scalar(DEFINE:([Expr1014]=[Expr1013]+(1)))
                      |    |--Table Spool(WITH STACK)
                      |--Index Seek(OBJECT:([local_dbname].[dbo].[Entity].[EntityParentId] AS [e]), SEEK:([e].[PId]=[Recr1004]) ORDERED FORWARD)

我尝试对实体pk进行重组，并运行sp_updatestats，但没有什么不同。

也尝试添加一个优化提示，但似乎没有得到它，例如

DECLARE @TopLevelEnt int
SET @TopLevelEnt = 187317;
select * from adams_test_view
WHERE eqId = @TopLevelEnt
OPTION (OPTIMIZE FOR (@TopLevelEnt = 187317))

我正在SQL Server Management Studio Express 2008 R2 的SQL Server 2005 Express数据库上运行此程序

任何暗示或技巧都将不胜感激。

Answer 1

您看到的是正常的，因为视图首先返回所有内容，然后查看变量并进行相应的过滤。当它被硬编码时，它会选择另一个计划，因为它从一开始就知道要应用哪个过滤器

您是否尝试过在cte的上选择部分使用带过滤器的内联表值函数执行同样的操作？