查询自动优化带来的负面影响--where条件执件顺序被打乱
一段查SQL前一天 SQL日志文件个数的语句
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
with sr
as
(
SELECT physical_device_name,RIGHT(physical_device_name,CHARINDEX('\',REVERSE(physical_device_name))-1) as t1
from backupmediafamily
where
left(right(physical_device_name,18),8)='20100728'--这是条件1 ,用来找到符合条件的记录集
and
cast(left(right(physical_device_name,18),12) asbigint) between201007281600and201007291559- 条件2
)
selectleft(t1,len(t1)-19) s1,count(1) s2 from sr groupbyleft(t1,len(t1)-19)
as
(
SELECT physical_device_name,RIGHT(physical_device_name,CHARINDEX('\',REVERSE(physical_device_name))-1) as t1
from backupmediafamily
where
left(right(physical_device_name,18),8)='20100728'--这是条件1 ,用来找到符合条件的记录集
and
cast(left(right(physical_device_name,18),12) asbigint) between201007281600and201007291559- 条件2
)
selectleft(t1,len(t1)-19) s1,count(1) s2 from sr groupbyleft(t1,len(t1)-19)
但是正常的需求是跨天的(时区+8的问题)
换成IN条件后变成 在SQL2008中 会自动先执行 条件2 (sql2005应该也会这样,因为它也是自动查询优化)
因为没有执行条件1 就执行2 所以在CAST的时候会出错
消息 8114,级别 16,状态 5,第 2 行
从数据类型 nvarchar 转换为 bigint 时出错。
SQL:
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
with sr
as
(
SELECT physical_device_name,RIGHT(physical_device_name,CHARINDEX('\',REVERSE(physical_device_name))-1) as t1
from backupmediafamily
where
left(right(physical_device_name,18),8) in( '20100728', '20100729') --这是条件1 ,用来找到符合条件的记录集
and
cast(left(right(physical_device_name,18),12) asbigint) between201007281600and201007291559-- 这是条件2,如果条件1 不成立 此句会错
)
selectleft(t1,len(t1)-19) s1,count(1) s2 from sr groupbyleft(t1,len(t1)-19)
解决的办法:
1。强制WHERE执行顺序 不让数据库引擎自动优化
2。变换SQL的写法
有没有更好的解决方法?