sql-server – 应该在存储过程中避免常量的局部变量？

即代替

WHERE [Order].OrderType = 3

我做

DECLARE @OrderType_Cash AS int = 3;
...
WHERE [Order].OrderType = @OrderType_Cash

这工作正常,我没有注意到通常使用的查询和数据的大小的任何性能问题.

最近我读了一篇关于参数嗅探和解决方法的文章(https://blogs.msdn.microsoft.com/turgays/2013/09/10/parameter-sniffing-problem-and-possible-workarounds/).在谚语中,提出的解决方法之一是“使用局部变量”.

4. Workaround : Use local variable – This workaround is very similar with previous one (OPTION (OPTIMIZE FOR (@VARIABLE UNKNOWN))) – when
   you assign the paramaters to local ones SQL Server uses statistic
   densities instead of statistic histograms – So It estimates the same
   number of records for all paramaters – The disadvantage is that some
   queries will use suboptimal plans because densities are not precise
   enough as the statistic histogram.

这让我有点担心,因为我的解释是,我可能会在我的存储过程中得到一个次优的计划,因为我使用一个局部变量而不是一个“魔术数字”.

我也觉得SQL Server会自动将“魔术数字”转换为变量,以重用计划.

有人可以为我清除这个吗？

使用“魔术数字”和局部变量有区别吗？
>如果是,只有在存储过程中,还是适用于即席查询和动态SQL？
使用像我这样的局部变量是不好的习惯吗？

解决方法

If you use a local variable in a query predicate instead of a
parameter or literal,the optimizer resorts to a reduced-quality
estimate,or a guess for selectivity of the predicate. Use parameters
or literals in the query instead of local variables

关于你的问题…

I was also under the impression that SqlServer automatically convert “magic numbers” into variables in order to reuse plans.

不,从来没有,它可以auto parameterise adhoc查询,但参数的行为与变量不同,可以被嗅探.默认情况下,只有在非常有限的情况下才会“安全”,并且不太可能引入参数嗅探问题.

Is there a difference between using a “magic number” and a local
variable?

是的,该语句通常在变量值均匀分配之前编译.即使该语句需要延迟编译(或者在分配之后被重新编译),除非使用选项(重新编译),变量的值仍然不会被嗅探.如果您使用文字内联SQL Server可以查看直方图中的文字值,并可能获得更准确的估计值,而不是求助于猜测.准确的行估计对于获得正确的整体计划形状(例如联接类型或访问方法选择)以及为查询获取适当的内存授予很重要.

本书“SQL Server 2005实际故障排除”有此问题可以说.

In SQL Server 2005,statement level compilation allows for compilation
of an individual statement in a stored procedure to be deferred until
just before the first execution of the query. By then the local
variable’s value would be known. Theoretically SQL Server could take
advantage of this to sniff local variable values in the same way that
it sniffs parameters. However because it was common to use local
variables to defeat parameter sniffing in SQL Server 7.0 and SQL
Server 2000+,sniffing of local variables was not enabled in SQL
Server 2005. It may be enabled in a future SQL Server release though

(注意：至今没有启用任何版本)

If yes,is it only in stored procedures or does it also
apply to ad-hoc queries and dynamic sql?

这适用于每一次使用变量.可以嗅探参数,如果您要将外部作用域中的变量作为内部范围中的参数传递,以允许嗅探变量值.

Is it a bad habit to use local variables like I do?

如果计划对于确切的变量值比对是敏感的.在某些地方,这将是完全无害的.

选项(重新编译)作为修复的缺点是每次重新编译语句.这是不必要的,当这样做的唯一原因是让它嗅探一个变量的值是不变的.选项(优化)具有特定文字值的缺点是,如果值更改,则需要更新所有这些引用.

另一种方法是创建一个常量视图.

CREATE VIEW MyConstants
AS
SELECT 3 AS OrderTypeCash,4 AS OrderTypeCard

然后,代替使用一个变量,而不是使用这个变量.

WHERE [Order].OrderType = (SELECT OrderTypeCash FROM MyConstants)

这将允许该值在编译时解决,只需要在一个地方进行更新.

或者,如果使用SSDT和数据库项目,则可以使用一次定义的sqlcmd变量,然后分配给该变量,然后将其替换为所有TSQL变量引用.部署到服务器的代码仍然具有“魔术数字”,但在源代码中,它是一个单一的SqlCmd变量(注意：对于此模式,您可能需要在项目中创建一个存根过程,并使用后期部署脚本来实际更改它具有所需的定义并执行sqlcmd替换).

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!