OPTION(RECOMPILE) 的一些疑问

m0_73085365 2022-08-07 18:53:23

日常在业务中经常用到类似 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)   ,或者使用拼接 来规避这种情况呢,重编译按理来说应该是增加了开销,平时大家有用或者那些场景下使用这个参数吗。

 

...全文
191 5 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
猫狸嘎 2022-08-08
  • 打赏
  • 举报
回复 1

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

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

一个条件用判断,多个条件用动态脚本拼接
一个条件:
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

shinger126 2022-08-08
  • 举报
回复 1
@shinger126 尽量不要用这种where 字段=变量 or 变量='' 这种形式的方法来处理,索引会失效
吉普赛的歌 版主 2022-08-07
  • 打赏
  • 举报
回复 1

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

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧