较新版本的 SQL Server(在 SQL Server 2017 之后)是否优化了使用变量而不是所谓的“幻数”的存储过程中的查询?例如,而不是使用
SELECT ProductName, ProductDescription from Products
where ProductCategory in (2, 5)
我们可以使用
DECLARE @GADGETS_CATEGORY INT = 1
DECLARE @CLOTHES_CATEGORY INT = 5
SELECT ProductName, ProductDescription from Products
where ProductCategory in (@GADGETS_CATEGORY , @CLOTHES_CATEGORY)
并仍然受益于相同的查询优化?
我知道有一些关于这个主题的老问题,但很高兴知道这是否得到了改进。
是的,我知道您可以创建一个包含类别的表,与该表进行连接,然后在 WHERE 子句中使用字符串,但这不是重点。存储过程中不应存在幻数。例如,您应该能够编写此代码,并且 SQL Server 在编译 SP 时应该能够替换查询中的内联常量:
DECLARE @DEFINED_LIMIT_IN_MB SMALLINT = 5;
SELECT ImageName, ImageDescription from Images where ImageSize < @DEFINED_LIMIT_IN_MB
如果你想让它“嗅探”变量的值并产生与
那么您是否需要添加一个
OPTION (RECOMPILE)
- 但随后您需要为每次执行重新编译支付费用。通常,SQL 语句是成批编译的,因此在对变量进行任何赋值之前编译语句。
即使语句被重新编译或延迟编译(因此变量赋值已经发生),它仍然不会在没有提示的情况下嗅探变量值,因为赋值
OPTION RECOMPILE
给变量是避免参数嗅探问题的常用技术,这将打败这个。如果您想在一个地方定义这些常量并将它们内联并且查询优化器可以看到这些值,您可以考虑将它们放在视图、内联表值函数或内联标量 UDF 中。
TBH 所有这些都有点笨拙,但没有像你想要的那样内联的变量。
如果您使用 SSDT,那么另一个笨拙的选择是定义 SQL CMD 变量
然后您的程序可以将它们引用为
'$(CLOTHES_CATEGORY)'
etc,并且在发布时它将被替换为'5'
. 但这现在是字符串文字而不是整数文字,因此您需要验证它本身不会导致任何问题。为了与整数列进行比较,尽管我希望隐式转换int
在编译时得到解决。因此,顾名思义,变量不同于文字(又名常量)的一个问题是值可能会发生变化。因此,无法保证您在声明时赋予变量的值在运行时将保持不变,直到它在谓词中使用的部分为止。
因此,它并不像您最初假设的那么简单,即使在您的特定示例中它会如此,但您的示例只是关于如何在代码中使用变量的众多示例中的一个。文字只有一个用例,它们被定义为的确切值仅针对它们在代码中使用的确切位置。
为了让 SQL 引擎能够对变量执行您想要的操作,它需要等到运行时,在变量在谓词中使用之前对其进行评估,然后才能知道该值的真正含义。但是 SQL 引擎的工作方式是先构建查询计划,然后执行所述查询计划。评估参数的值只能在执行期间发生,但此时查询计划已经构建,无法在执行过程中动态更改。
正如您链接到的答案中提到的,可以
OPTION (RECOMPILE)
在使用变量的查询上使用提示,使其在运行时被嗅探。这会导致该查询不将其执行计划存储在计划缓存中,而是根据运行时变量的值生成一个新计划。使用该提示会产生一些开销,但如果您的查询不是非常频繁地运行(例如每秒 100 次以上),通常就可以了。