OPTION(RECOMPILE) 的一些疑问

[m0_73085365]

日常在业务中经常用到类似 WHERE Column = @Param OR @Param IS NULL 的语句

 

CREATE TABLE Test

( SomeCol1 INT NOT NULL ,

Somecol2 INT NOT NULL )



INSERT Test SELECT number , low

FROM master..spt_values WHERE TYPE = 'p'



CREATE INDEX ix_col2 ON Test(Somecol2) 
GO

这时查询时

DECLARE @col INT
SELECT @col = 1
 
SELECT SomeCol2 
FROM Test
WHERE SomeCol2=@col OR @col IS NULL

看查询计划的结果是 全表扫描

但是如果添加OPTION(RECOMPILE)  后

出来的查询计划是索引查找。

麻烦问下大家是什么造成了这种情况呢？是否只有添加OPTION(RECOMPILE)   ，或者使用拼接 来规避这种情况呢，重编译按理来说应该是增加了开销，平时大家有用或者那些场景下使用这个参数吗。

 

[吉普赛的歌]

你这种写法，纯粹是开发人员的一种偷懒的做法，不可取。
一个条件还好，条件多了呢？加OPTION(RECOMPILE) 可能都走不了索引
拼SQL才是正确做法，程序里或存储过程里拼都行。
有用的条件才加上，没用的不加，不让SQL server 做无意义的事：帮你来判断条件到底要不要加

[shinger126]

@shinger126 尽量不要用这种where 字段=变量 or 变量='' 这种形式的方法来处理，索引会失效

[shinger126]

一个条件用判断，多个条件用动态脚本拼接
一个条件：
if @col is not null
SELECT SomeCol2
FROM Test
WHERE SomeCol2=@col
多个条件:
declare @sql nvarchar(max)
set @sql=N'SELECT SomeCol2
FROM Test where 1=1'+case when @col>'' then ' and SomeCol2='''+@col+'''' else '' end

• case when @col3>'' then ' and SomeCol3='''+@col3+'''' else '' end
  exec sp_executesql @sql

[猫狸嘎]

@猫狸嘎 请教了些大佬，查了些资料，个人理解是option(re比with re多一个特性，"Parameter Embedding Optimization"，这个特性导致它能把本地变量当做常量来生成执行计划，这样它就能充分利用统计信息决定是scan还是seek。虽然并不知道为什么加入inner join 或者cross apply也能走seek，一定还是很多东西我还没触及到。

[猫狸嘎]

楼上大佬们说了不少，不过针对楼主提问的“是什么造成了这种情况呢？”。没有做针对说明，萌新这里随便瞎猜点，若有错误还请指点。
对于查询，sqlserver区分了"简单"查询和"正常"查询。基于此概念，我有两个猜测：
1、“简单”的查询用另外一套流程规则生成查询计划，它生成查询计划时，无法优化条件中的“or 变量 运算符 值”。
2、没有联结查询的属于“简单”查询
我是基于什么提出这两个猜测的？
1、如果你用查询提示with(forceseek)，此时它会提示：“....查询处理器未能生成查询计划.....”。而你改为“OR SomeCol2 IS NULL ”或者“OR 1 IS NULL ”，它又行了。
2、如果你引入联结查询譬如inner join或cross apply，它会走seek，不再报错。但是left join仍然会报错，这一点想不通。