如果在WHERE子句中，常量被参数（具有相同值）替换，为什么查询会大幅减慢？

I have a recursive query which executes very fast if the WHERE clause contains a constant but becomes very slow if I replace the constant with a parameter having the same value.

我有一个递归查询，如果WHERE子句包含常量，则执行速度非常快，但如果我用一个具有相同值的参数替换常量，则会变得非常慢。

Query #1 - with constant

查询＃1 - 使用常量

;WITH Hierarchy (Id, ParentId, Data, Depth)
AS
( SELECT Id, ParentId, NULL AS Data, 0 AS Depth
  FROM Test
  UNION ALL
  SELECT h.Id, t.ParentId, COALESCE(h.Data, t.Data), Depth + 1 AS Depth
  FROM Hierarchy h
       INNER JOIN Test t ON t.Id = h.ParentId
)
SELECT *
FROM Hierarchy
WHERE Id = 69

Query #2 - with parameter

查询＃2 - 带参数

DECLARE @Id INT
SELECT @Id = 69

;WITH Hierarchy (Id, ParentId, Data, Depth)
AS
( SELECT Id, ParentId, NULL AS Data, 0 AS Depth
  FROM Test
  UNION ALL
  SELECT h.Id, t.ParentId, COALESCE(h.Data, t.Data), Depth + 1 AS Depth
  FROM Hierarchy h
       INNER JOIN Test t ON t.Id = h.ParentId
)
SELECT *
FROM Hierarchy
WHERE Id = @Id

In case of a table with 50,000 rows the query with the constant runs for 10 milliseconds and the one with the parameter runs for 30 seconds (3,000 times slower).

对于具有50,000行的表，具有常量的查询运行10毫秒，具有该参数的查询运行30秒（慢3000倍）。

It is not an option to move the last WHERE clause to the anchor definition of the recursion, as I would like to use the query to create a view (without the last WHERE). The select from the view would have the WHERE clause (WHERE Id = @Id) - I need this because of Entity Framework, but that is another story.

将最后一个WHERE子句移动到递归的锚定义不是一个选项，因为我想使用查询来创建一个视图（没有最后一个WHERE）。从视图中选择将具有WHERE子句（WHERE Id = @Id） - 我需要这个因为实体框架，但这是另一个故事。

Can anybody suggest a way to force query #2 (with the parameter) to use the same query plan as query #1 (with the constant)?

任何人都可以建议一种方法来强制查询＃2（使用参数）使用与查询＃1相同的查询计划（使用常量）？

I already tried playing with indexes but that did not help.

我已经尝试过使用索引，但这并没有帮助。

If somebody would like I can post the table definition and some sample data as well. I am using SQL 2008 R2.

如果有人想我可以发布表定义和一些示例数据。我正在使用SQL 2008 R2。

Thank you for your help in advance!

提前谢谢你的帮助！

Execution plan - Query #1 - with constant

执行计划 - 查询＃1 - 使用常量

Execution plan - Query #2 - with parameter

执行计划 - 查询＃2 - 带参数

4 个解决方案

#1

As Martin suggested in a comment under the question, the problem is that SQL server does not push down properly the predicate from the WHERE clause - see the link in his comment.

正如Martin在问题评论中所建议的那样，问题是SQL服务器没有正确地从WHERE子句中正确推断出谓词 - 请参阅他的评论中的链接。

I ended up with creating a user defined table-valued function and use it with the CROSS APPLY operator for creating the view.

我最终创建了一个用户定义的表值函数，并将其与CROSS APPLY运算符一起用于创建视图。

Let's see the solution itself.

让我们看看解决方案本身。

User Defined Table-valued Function

用户定义的表值函数

CREATE FUNCTION [dbo].[TestFunction] (@Id INT)
RETURNS TABLE 
AS
RETURN 
(
    WITH
    Hierarchy (Id,  ParentId, Data, Depth)
    AS(
    SELECT Id, ParentId, NULL AS Data, 0 AS Depth FROM Test Where Id = @Id
    UNION ALL
    SELECT h.Id, t.ParentId, COALESCE(h.Data, t.Data), Depth + 1 AS Depth
        FROM Hierarchy h
            INNER JOIN Test t ON t.Id = h.ParentId
    )
    SELECT * FROM Hierarchy
)

View

视图

CREATE VIEW [dbo].[TestView]
AS
SELECT t.Id, t.ParentId, f.Data, f.Depth
FROM
    Test AS t
    CROSS APPLY TestFunction(Id) as f

Query with constant

用常量查询

SELECT * FROM TestView WHERE Id = 69

Query with parameter

查询参数

DECLARE @Id INT
SELECT @Id = 69
SELECT * FROM TestView WHERE Id = @Id

The query with the parmater executes basically as fast as the query with the constant.

使用parmater的查询基本上与使用常量的查询一样快。

Thank You Martin and for the others as well!

谢谢Martin和其他人！

#2

For your second Query try using the OPTIMIZE FOR or OPTION(RECOMPILE) query hint to see if that forces it to recomplile based on the provided parameter value.

对于您的第二个查询尝试使用OPTIMIZE FOR或OPTION（RECOMPILE）查询提示，以查看是否强制它根据提供的参数值重新取代。

#3

You should use a plan guide to freeze the plan you want.

您应该使用计划指南冻结您想要的计划。

#4

This could be the worse suggestion ever, but have you considered creating a sproc to create your query as a string and execute it using sp_executesql?

这可能是有史以来最糟糕的建议，但您是否考虑过创建一个sproc来创建查询作为字符串并使用sp_executesql执行它？

I know nothing about the caching behaviour of SQL executed by sp_executesql, it was just the first thing to pop into my head.

我对sp_executesql执行的SQL的缓存行为一无所知，这只是我头脑中的第一件事。