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

一尘不染

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

sql

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

Query #1 - with constant

;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

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倍）。

不能将lastWHERE子句移至
递归的锚定义，因为我想使用查询来创建视图（没有
last WHERE）。从视图中进行选择将具有WHERE子句
（WHERE Id = @Id）-由于Entity Framework，我需要此子句，但这是
另一回事了。

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

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

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

提前谢谢你

阅读 230

2021-03-17

共1个答案

一尘不染

正如Martin在该问题下的注释中所建议的那样，问题在于SQL
Server无法正确按下WHERE子句中的谓词-请参阅
他的注释中的链接。

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

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

用户定义的表值函数

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

使用参数的查询的执行速度基本上与使用常量的查询一样快。

谢谢你马丁和其他人！

2021-03-17