none
SQL写法导致的SQL2008性能问题 RRS feed

  • 问题

  • 以下是两种不同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

    2012年6月14日 7:04

答案

  • 造成这种情况原因可能:

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

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

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

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

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

    2012年6月15日 1:28
  • @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'


    2012年6月15日 7:10

全部回复

  • 您好,

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

    我有测试,我的执行计画是相同的!

    您可在执行前先执行

    DBCC FREEPROCCACHE WITH NO_INFOMSGS;

    看看速度是否还是有差别哦!


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

    2012年6月14日 7:47
  • 可能是数据没有缓存,物理读影响性能,你可以把第一种写法再执行几次看看。

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

    2012年6月14日 7:51
    版主
  • 看看执行计划有多大的差异

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

    2012年6月14日 8:02
  • 另外一个就是提到了升级,那么在升级之后是否有做 indx rebuild 操作,如果没有的话,请先做一次
    2012年6月14日 8:03
  • 这种情况我一般喜欢用between and 

    where adate between @badate and @eadate

    2012年6月14日 15:10
    版主
  • 静下心来做了下测试,最终的结论如下

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

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

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

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


    jack

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

    2012年6月15日 0:00
  • 造成这种情况原因可能:

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

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

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

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

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

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


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

    family as water

    2012年6月15日 3:09
  • @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'


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

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




    2012年6月15日 7:11
  • 1、不知这个嗅探理论正解如何,能否在这里给扫盲下

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

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


    jack


    2012年6月15日 16:39
  • 你说的这种情况我也遇到过,解决方法相同,但比较怪异的是我并不是每个库都这样子,虽然是不同业务库,但表结构,数据库版本都相同

    jack


    2012年6月15日 16:46
  • 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

    2012年6月16日 1:25
  • 你说的这种情况我也遇到过,解决方法相同,但比较怪异的是我并不是每个库都这样子,虽然是不同业务库,但表结构,数据库版本都相同

    jack


    我不知道你具体说的是什么?但除了表结构,数据库版本,还有很多的因素很多的因素会影响到执行。就算你同一个语句,前一秒的执行跟后一秒的执行性能相差很大也是很正常的。具体情况具体分析。
    2012年6月16日 1:28
  • 直接贴上来执行计划看看。
    2012年6月16日 8:20