我有一个执行得很糟糕的存储过程。当我声明一个变量时,设置它的值,然后在 where 子句中使用它,该语句需要一个多小时才能运行。当我对 where 子句中的变量进行硬编码时,它的运行时间不到一秒。
我开始通过执行计划来查找问题所在。看起来当我尝试向它传递一些声明的变量时,执行计划会创建一些哈希匹配,因为它从使用 UNION 和公共表表达式的视图中选择值。
/************* Begin of Stored Procedure ***************/
CREATE PROCEDURE GetFruit
@ColorId bigint,
@SeasionId bigint
WITH RECOMPILE
AS
BEGIN
SELECT
A.Name
FROM
[Apple_View] A /* This is the view down below */
INNER JOIN [Fruit] F
ON ( F.ColorId = @ColorId
AND A.FruitId = F.FruitId)
WHERE
(A.ColorId = @ColorId
AND
A.SeasonId = @SeasonId)
END
/************* End of Stored Procedure ***************/
/************* Begin of View ***************/
WITH Fruits (FruitId, ColorId, SeasonId) AS
(
-- Anchor member
SELECT
F.FruitId
,F.ColorId
,F.SeasonId
FROM
((
SELECT DISTINCT
EF.FruitId
,EF.ColorId
,EF.SeasonId
,EF.ParentFruitId
FROM
ExoticFruit EF
INNER JOIN Fruit FR
ON FR.FruitId = EF.FruitId
UNION
SELECT DISTINCT
SF.FruitId
,SF.ColorId
,SF.SeasonId
,SF.ParentFruitId
FROM
StinkyFruit SF
INNER JOIN Fruit FR
ON FR.FruitId = SF.FruitId
UNION
SELECT DISTINCT
CF.FruitId
,CF.ColorId
,CF.SeasonId
,CF.ParentFruitId
FROM
CrazyFruit CF
INNER JOIN Fruit FR
ON FR.FruitId = CF.FruitId
)) f
UNION ALL
-- Recursive Parent Fruit
SELECT
FS.FruitId
,FS.ColorId
,FS.SeasonId
,FS.ParentFruitId
FROM
Fruits FS
INNER JOIN MasterFruit MF
ON MF.[ParentFruitId] = fs.[FruitId]
)
SELECT DISTINCT
FS.FruitId
,FS.ColorId
,FS.SeasonId
FROM
Fruits FS
/************* End of View ***************/
/* To Execute */
EXEC GetFruit 1,3
If I run the Stored Procedure using the set values it takes over an hour and here is the execution plan.
![With Variables](https://i.stack.imgur.com/FfMq2.png)
如果我运行删除 DECLARE 和 SET 值的存储过程,并将Where子句设置为以下语句,它将在不到一秒的时间内运行,这是执行计划:
WHERE(A.ColorId = 1 AND A.SeasonId = 3)
请注意硬编码变量如何使用索引,而第一个变量使用哈希集。这是为什么?为什么 where 子句中的硬编码值与声明的变量不同?
--------这就是在@user1166147的帮助下最终执行的--------
我将存储过程更改为使用 sp_executesql。
CREATE PROCEDURE GetFruit
@ColorId bigint,
@SeasionId bigint
WITH RECOMPILE
AS
BEGIN
DECLARE @SelectString nvarchar(max)
SET @SelectString = N'SELECT
A.Name
FROM
[Apple_View] A /* This is the view down below */
INNER JOIN [Fruit] F
ON ( F.ColorId = @ColorId
AND A.FruitId = F.FruitId)
WHERE
(A.ColorId = ' + CONVERT(NVARCHAR(MAX), @ColorId) + '
AND
A.SeasonId = ' + CONVERT(NVARCHAR(MAX), @SeasonId) + ')'
EXEC sp_executesql @SelectString
END