sql – 为什么如果在WHERE子句中一个常量被一个参数替换(具有相同的值),那么查询会大大减慢?

前端之家收集整理的这篇文章主要介绍了sql – 为什么如果在WHERE子句中一个常量被一个参数替换(具有相同的值),那么查询会大大减慢?前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我有一个递归查询,如果WHERE子句包含一个常量,执行速度非常快,但如果用具有相同值的参数替换常量,则会变得非常慢.

查询#1 – 具有常量

;WITH Hierarchy (Id,ParentId,Data,Depth)
AS
( SELECT Id,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,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倍).

将最后一个WHERE子句移动到递归的锚定定位不是一个选择,因为我想使用该查询创建一个视图(没有最后一个WHERE).从视图中选择将有WHERE子句(WHERE Id = @Id) – 我需要这个因为Entity Framework,但这是另一个故事.

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

我已经尝试玩索引,但没有帮助.

如果有人想我可以发布表定义和一些样本数据.
我正在使用sql 2008 R2.

谢谢你的帮助提前!

执行计划 – 查询#1 – 具有常量

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

解决方法

正如Martin在问题的意见中建议的那样,问题是sql Server没有正确地从WHERE子句中正确地推下谓词 – 请参阅他的评论中的链接.

最后我创建一个用户定义的表值函数,并用CROSS APPLY操作符来创建视图.

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

用户定义的表值函数

CREATE FUNCTION [dbo].[TestFunction] (@Id INT)
RETURNS TABLE 
AS
RETURN 
(
    WITH
    Hierarchy (Id,Depth)
    AS(
    SELECT Id,0 AS Depth FROM Test Where Id = @Id
    UNION ALL
    SELECT h.Id,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,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

与parmator的查询执行基本上与使用常量的查询一样快.

谢谢马丁和其他人!

原文链接:https://www.f2er.com/mssql/75290.html

猜你在找的MsSQL相关文章