参数的性能不如对值进行硬编码 [英] Parameter doesn't perform as well as hard coding the value
问题描述
我有一个性能很差的存储过程.当我声明一个变量时，设置它的值，然后在 where 子句中使用它，该语句需要一个多小时才能运行.当我对 where 子句中的变量进行硬编码时，它会在不到一秒的时间内运行.
我开始通过执行计划调查它的问题所在.看起来当我尝试将一些声明的变量传递给它时，执行计划会创建一些哈希匹配，因为它从使用 UNION 和公共表表达式的视图中选择值.
<前>/************* 存储过程开始 ***************/创建程序 GetFruit@ColorId bigint，@SeasionId bigint重新编译作为开始选择一个名字从[Apple_View] A/* 这是下面的视图 */INNER JOIN [水果] FON ( F.ColorId = @ColorIdAND A.FruitId = F.FruitId)在哪里(A.ColorId = @ColorId和A.SeasonId = @SeasonId)结尾/************* 存储过程结束 ****************//************* 视图开始 ****************/WITH Fruits (FruitId, ColorId, SeasonId) AS(-- 主播成员选择F.FruitId,F.ColorId,F.SeasonId从((选择不同EF.FruitId,EF.ColorId,EF.SeasonId,EF.ParentFruitId从异域水果 EFINNER JOIN 水果 FRON FR.FruitId = EF.FruitId联盟选择不同SF.FruitId,SF.ColorId,SF.SeasonId,SF.ParentFruitId从臭水果 SFINNER JOIN 水果 FRON FR.FruitId = SF.FruitId联盟选择不同CF.FruitId,CF.ColorId,CF.SeasonId,CF.ParentFruitId从疯狂水果INNER JOIN 水果 FRON FR.FruitId = CF.FruitId)) F联合所有-- 递归父果选择FS.FruitId,FS.ColorId,FS.SeasonId,FS.ParentFruitId从水果FS内部连接 MasterFruit MFON MF.[ParentFruitId] = fs.[FruitId])选择不同FS.FruitId,FS.ColorId,FS.SeasonId从水果FS/************* 查看结束 ***************//* 执行 */EXEC GetFruit 1,3如果我使用设置的值运行存储过程，则需要一个多小时，这是执行计划.
如果我运行存储过程并删除 DECLARE 和 SET 值并将 Where 子句设置为以下语句，它会在不到一秒的时间内运行，这是执行计划:
WHERE(A.ColorId = 1 AND A.SeasonId = 3)注意硬编码变量如何使用索引，而第一个使用哈希集.这是为什么?为什么 where 子句中的硬编码值与声明的变量不同?
-------这是在@user1166147的帮助下最终完成的------
我将存储过程更改为使用 sp_executesql.
<前>创建程序 GetFruit@ColorId bigint，@SeasionId bigint重新编译作为开始声明 @SelectString nvarchar(max)SET @SelectString = N'SELECT一个名字从[Apple_View] A/* 这是下面的视图 */INNER JOIN [水果] FON ( F.ColorId = @ColorIdAND A.FruitId = F.FruitId)在哪里(A.ColorId = ' + CONVERT(NVARCHAR(MAX), @ColorId) + '和A.SeasonId = ' + CONVERT(NVARCHAR(MAX), @SeasonId) + ')'EXEC sp_executesql @SelectString结尾编辑摘要 根据 Damien_The_Unbeliever 的请求
目标是在创建计划之前获取有关 SQL 变量值的最佳/最多信息，通常参数嗅探会执行此操作.在这种情况下，参数嗅探被禁用"可能是有原因的.如果没有看到实际代码的更好表示，我们就无法真正说出解决方案是什么或问题存在的原因.尝试以下方法以强制受影响区域使用实际值生成计划.
*更详细的长版本*
这是您实际的存储过程吗?你的参数有默认值吗?如果有，它们是什么?
参数嗅探可以提供帮助 - 但它必须具有典型的参数值才能很好地创建计划，如果没有，则不会真正有帮助，或者会根据非典型参数值创建一个糟糕的计划.因此，如果变量在第一次运行和编译计划时具有默认值 null 或不是典型值的值 - 它会创建一个错误的计划.
如果其他人编写了这个 sproc - 他们可能出于某种原因故意禁用"了局部变量的参数嗅探.业务规则可能需要这些可变结构.
目标是在创建计划之前获取有关 SQL 变量值的最佳/最多信息，通常参数嗅探会执行此操作.但是有些事情会使其对性能产生负面影响，这可能就是它被禁用"的原因.似乎仍在创建计划时使用参数的非典型值或仍然没有足够的信息 - 是否使用参数嗅探.
尝试在 sproc 内部调用查询，使用 Use sp_executesql 执行受影响的查询，强制它使用实际变量为该区域生成计划，看看是否更好.如果您必须拥有这种不规则的参数值，这可能是您的解决方案 - 创建运行受影响部分的存储过程并稍后从存储过程中调用它们 - 在变量收到典型值之后.
如果没有看到实际代码的更好表示，就很难看出问题是什么.希望这些信息会有所帮助 -
I have a stored procedure that performs terribly. When I declare a variable, set its value and then use it in the where clause the statement takes over an hour to run. When I hard code the variables in the where clause it runs in less than a second.
I started to look into what was wrong with it through execution plans. It looks like when I try and pass it some declared variables the execution plan crates some Hash Match because it selects values from a view that uses a UNION and a common table expression.
/************* 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.
If I run the Stored Procedure removing the DECLARE and SET values and just set the Where clause to the following statement it runs in less than a second and here is the execution plan:
WHERE(A.ColorId = 1 AND A.SeasonId = 3)
Notice how the hard coded variables uses indexing while the first uses a hash set. Why is that? Why are hard coded values in the where clause working different from the declared variables?
-------this is what finally performed with the help of @user1166147------
I changed the stored procedure to use 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
EDIT SUMMARY Per a request from Damien_The_Unbeliever
The goal is to get best/most information about the variable value to SQL BEFORE the plan is created, generally parameter sniffing does this. There may be a reason that parameter sniffing was 'disabled' in this case. Without seeing a better representation of the actual code we can't really say what the solution is or why the problem exists. Try the things below to force the affected areas to generate plans using actual values.
*LONG VERSION WITH MORE DETAIL *
Is this your actual stored proc? Do you have default values for your parameters? If so, what are they?
Parameter sniffing can help - but it has to have typical parameters values to create the plan well, and if not, won't really help or will create a bad plan based off of the non typical parameter value. So if a variable has a default value of null or a value that is not a typical value the first time it is run and the plan compiled - it creates a bad plan.
If someone else wrote this sproc - they may have intentionally 'disabled' parameter sniffing with the local variables for a reason. Business rules may require these variable structures.
The goal is to get best/most information about the variable value to SQL BEFORE the plan is created, and generally Parameter Sniffing does this. But there are things that can make it affect performance negatively, and that may be why it is 'disabled'. It still seems like the plan is being created with atypical values for the parameters or not enough info still - using parameter sniffing or not.
Try calling the query inside the sproc with Use sp_executesql to execute the affected queries, forcing it to generate a plan for that area with the actual variables, and see if it's better. This may be your solution if you have to have this sort of irregular parameter value - create stored procs that run the affected parts and call them later from within the stored procedure - after the variable has received a typical value.
Without seeing a better representation of the actual code, it is hard to see what the problem is. Hopefully this info will help -
这篇关于参数的性能不如对值进行硬编码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
