如果 WHERE 子句中的常量被参数(具有相同的值)替换，为什么查询会急剧变慢? [英] Why does a query slow down drastically if in the WHERE clause a constant is replaced by a parameter (having the same value)?

问题描述

我有一个递归查询，如果 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.

查询 #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

查询 #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

如果表有 50,000 行，则使用常量的查询运行 10 毫秒，使用参数的查询运行 30 秒(慢 3,000 倍).

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).

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

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.

有人可以建议一种方法来强制查询 #2(带有参数)使用与查询 #1(带有常量)相同的查询计划吗?

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

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

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

如果有人愿意，我也可以发布表定义和一些示例数据.我使用的是 SQL 2008 R2.

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

提前感谢您的帮助！

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

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

推荐答案

正如 Martin 在问题下的评论中所建议的，问题是 SQL Server 没有正确下推 WHERE 子句中的谓词 - 请参阅他的链接评论.

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.

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

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

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

Let's see the solution itself.

用户定义的表值函数

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
)

查看

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

使用常量查询

SELECT * FROM TestView WHERE Id = 69

带参数查询

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.

感谢 Martin 和其他人！

Thank You Martin and for the others as well!

这篇关于如果 WHERE 子句中的常量被参数(具有相同的值)替换，为什么查询会急剧变慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！