SQL写法导致的SQL2008性能问题

Question

以下是两种不同SQL写法，得到的结果是相当，但执行效率却差跑好大

第一种写法

declare @badate datetime,

            @eadate datetime

set @badate='2012-06-01' 

set @eadate='2012-06-02'

select * from test where adate>=@badate and adate<=@eadate

第二种写法

select * from test where adate>='2012-06-01' and adate<='2012-06-02'

2000升级到2008后的数据库中执行，第一种执行了将近2分钟，而后一种只执行1秒不到，百思不得其解，望达人解释一下，第二种写法改成动态语句的写法执行效率也是不足1秒（注2000升级后索引及统计信息均已更新）

---

jack

Answer 1

您好，

您可以看一下执行计画，看2种的SQL是否相同就可得知差异点了！

我有测试，我的执行计画是相同的！

您可在执行前先执行

DBCC FREEPROCCACHE WITH NO_INFOMSGS; 
看看速度是否还是有差别哦！

---

以上說明若有錯誤請指教，謝謝。
亂馬客blog: http://www.dotblogs.com.tw/rainmaker/

Answer 2

可能是数据没有缓存，物理读影响性能，你可以把第一种写法再执行几次看看。

---

想不想时已是想，不如不想都不想。

Answer 3

看看执行计划有多大的差异

另外，确认一下表结构，你的addate 字段是否是 datetime 类型，如果不是的话，这可能会因为数据类型转换带来一些问题

Answer 4

另外一个就是提到了升级，那么在升级之后是否有做 indx rebuild 操作，如果没有的话，请先做一次

Answer 5

这种情况我一般喜欢用between and 

where adate between @badate and @eadate

Answer 6

静下心来做了下测试，最终的结论如下

1、2000和2008对执行计划的处理有不同，导致同样的语句出现的不同的执行效果，真有是点坑爹，丫的难不成升一次级把所有报表都重新写一遍啊

2、SP_EXECUTESQL还是挺好用，对比几种写法，用这个函数执行效率远高于普通的写法，减少编译次数效果还是很明显的

3、BETWEEN ...AND...对于给出的用例，和>= and <?这种写法，效率是等同的，并没啥差别

4、用2008快半年了，感觉除了复制订阅和压缩备份体验比较好外，其它没感受出优点来，反而是一堆麻烦

---

jack

Answer 7

确实感觉有些地方越改越不尽人意，曾经遇到过与楼主比较相似的问题，我遇到的问题是存储过程参数，直接引用参数在条件中，性能极差，定义一个变量，在存储过程里面把参数赋值给变量，再在查询中引用变量，效果就很好，而且是在多个环境的服务器测试的结果都一样，所以这种应该归结为查询优化器的自满有问题吧

Answer 8

造成这种情况原因可能：

1.数据量太大，但数据密度分布不均匀

2.数据量大，变量如果是以前的值，比如'2012-01-01'，此时不会出现index scan的情况，则说明统计信息更新太慢，需要手工更新统计信息，甚至做成job，半小时更新一次。

如果更新统计信息无效，多半需要强制索引了：

select * from test with(nolock,index=adate索引名称) where adate>=@badate and adate<=@eadate

不过强制索引也会有聚集scan和(非聚集index+key查询)到底谁消耗资源更多的问题，这里就会存在取舍的问题。

Answer 9

确实感觉有些地方越改越不尽人意，曾经遇到过与楼主比较相似的问题，我遇到的问题是存储过程参数，直接引用参数在条件中，性能极差，定义一个变量，在存储过程里面把参数赋值给变量，再在查询中引用变量，效果就很好，而且是在多个环境的服务器测试的结果都一样，所以这种应该归结为查询优化器的自满有问题吧

深有同感，记得参数化查询还会导致分区索引无效，和楼主的情况很类似，逼我们在项目中拼sql。。。

---

family as water

Answer 10

@jzhu_2000

你的问题其实满好解释的，SQL SERVER有参数嗅探的功能。

你的第一种写法SQL SERVER  在编译的时候，set @badate='2012-06-01' ，set @eadate='2012-06-02' 还没有被执行，所以它会评估10%的行数，很可能会导致表扫表。

假如你的第二种写法性能很好的话，那下面2种写法性能也一定会很好。

--1， SQL SERVER 无法嗅探，所以使用recompile。

select * from test where adate>=@badate and adate<=@eadate option(recompile)

--2，SQL SERVER 可以嗅探

CREATE PROCEDURE p1
 -- Add the parameters for the stored procedure here
   @badate datetime,
   @eadate datetime
AS
BEGIN
 SET NOCOUNT ON;
select * from test where adate>=@badate and adate<=@eadate
END
GO
 
--exec p1 '2012-06-01' ,'2012-06-02'

Answer 11

确实感觉有些地方越改越不尽人意，曾经遇到过与楼主比较相似的问题，我遇到的问题是存储过程参数，直接引用参数在条件中，性能极差，定义一个变量，在存储过程里面把参数赋值给变量，再在查询中引用变量，效果就很好，而且是在多个环境的服务器测试的结果都一样，所以这种应该归结为查询优化器的自满有问题吧

你的结论看上去跟SQL SERVER 参数嗅探理论似乎正好相反，要重现你的问题应该不会很难，不过这也应该跟参数嗅探有关。但不知道你的具体CASE是什么，能在这里重现你的问题吗？

Answer 12

1、不知这个嗅探理论正解如何，能否在这里给扫盲下

2、如果这个嗅探理论是从2000就开始有的话，那为啥在2000上查询性能未受到影响

3、你提到的两种写法也确实和我的第二种写法效率相同

---

jack

Answer 13

你说的这种情况我也遇到过，解决方法相同，但比较怪异的是我并不是每个库都这样子，虽然是不同业务库，但表结构，数据库版本都相同

---

jack

Answer 14

1、不知这个嗅探理论正解如何，能否在这里给扫盲下

2、如果这个嗅探理论是从2000就开始有的话，那为啥在2000上查询性能未受到影响

3、你提到的两种写法也确实和我的第二种写法效率相同

---

jack

我没有玩过2000。你的意思是说2000上的执行速度跟2000+（2005及其以上）一样快？ 你确信表结构，索引结构，还是数据分布情况也一样吗？我不知道2000知否支持SET STATISTICS PROFILE ON ，如果支持的话，你执行一下下面的语句，然后

1 把表头为“ Rows Executes StmtText StmtId NodeId Parent  。。。。。” 的结果贴出来，直接copy with headers,全部贴出来，乱了没关系。

2.把“消息“那个TAB下面的信息贴出来（Logical reads , physical reads字样的）
 
 SET STATISTICS PROFILE ON
  SET STATISTICS io on
 declare @badate datetime,
         @eadate datetime
 
set @badate='2012-06-01'
 set @eadate='2012-06-02'
select * from test where adate>=@badate and adate<=@eadate
  SET STATISTICS io off
SET STATISTICS PROFILE OFF

Answer 15

你说的这种情况我也遇到过，解决方法相同，但比较怪异的是我并不是每个库都这样子，虽然是不同业务库，但表结构，数据库版本都相同

---

jack

我不知道你具体说的是什么？但除了表结构，数据库版本，还有很多的因素很多的因素会影响到执行。就算你同一个语句，前一秒的执行跟后一秒的执行性能相差很大也是很正常的。具体情况具体分析。

Answer 16

直接贴上来执行计划看看。