问题描述
限时送ChatGPT账号..我有一个递归查询,如果 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屋!
更多推荐
[db:关键词]
发布评论