转载

sql server datediff函数引发的性能问题

    今天,帮一哥们反馈系统很慢,很卡,让我远程看一下。我远程过去查了一下数据库系统,发现很多阻塞,语句都基本相似,并且表的数据也不大,只有10多万条记录。
1、问题分析:
本系统是sql server数据库,仔细分析了该语句,就是想查询与当前日期差异为3分钟。就这么一个条件,由于使用的人多,所以造成了阻塞。
条件如下:
where datediff(mi,uptime,getdate())>=3
仔细查看了数据库的索引,并没有关于uptime的索引,再查看执行计划,该语句走的是全表扫描,但实际上即使使用索引,该语句也不会使用索引,因为针对此列添加了索引。

2、优化解决
将where后面的条件进行修改调整
修改为如下:
uptime <=  DateAdd(minute,-3,getdate())
另外,如果条件可以,再建议查询前将getdate()
@date = getdate()
这样调整后,再针对uptime字段建上索引,问题得到圆满解决,阻塞没有了。

3、关于两个函数的说明
3.1 dateiff使用说明
DateDiff 函数用于判断在两个日期之间存在的指定时间间隔的数目。例如可以使用 DateDiff 计算两个日期相差的天数,或者当天到当年最后一天之间的星期数。要计算 date1 和 date2 相差的天数,可以使用“一年的日数”(“y”)或“日”(“d”)。当 interval 为“一周的日数”(“w”)时,DateDiff 返回两个日期之间的星期数。如果 date1 是星期一,则 DateDiff 计算到 date2 之前星期一的数目。此结果包含 date2 而不包含 date1。如果 interval 是“周”(“ww”),则 DateDiff 函数返回日历表中两个日期之间的星期数。函数计算 date1 和 date2 之间星期日的数目。如果 date2 是星期日,DateDiff 将计算 date2,但即使 date1 是星期日,也不会计算 date1。
如果 date1 晚于 date2,则 DateDiff 函数返回负数。
firstdayofweek 参数会对使用“w”和“ww”间隔符号的计算产生影响。
如果 date1 或 date2 是日期文字,则指定的年度会成为日期的固定部分。但是如果 date1 或 date2 被包括在引号 (" ") 中并且省略年份,则在代码中每次计算 date1 或 date2 表达式时,将插入当前年份。这样就可以编写适用于不同年份的程序代码。
在 interval 为“年”(“yyyy”)时,比较 12 月 31 日和来年的 1 月 1 日,虽然实际上只相差一天,DateDiff 返回 1 表示相差一个年份。
下面的示例利用 DateDiff 函数显示今天与给定日期之间间隔天数:
Function DiffADate(theDate)
  DiffADate = "从当天开始的天数:" & DateDiff("d", Now, theDate)
End Function
由于该函数在查询使用过程中,经常会把列做为一个参数值,再加上另一个时间值进行比较,导致函数无法使用索引,很多情况下会导致性能下降

3.2 dateadd使用说明
可以使用 DateAdd 函数对日期加上或减去指定的时间间隔。例如,可以用 DateAdd 来计算距今天为三十天的日期;或者计算距现在为 45 分钟的时间。
为了对 date 加上“日”,可以使用“一年的日数” (“y”),“日” (”d”) 或“一周的日数” (”w”)。
DateAdd 函数将不返回有效日期。在以下实例中将 1 月31 日加上一个月:
DateAdd(m, 1, 31-Jan-95)
上例中,DateAdd 返回 1995 年 2 月 28 日,而不是 1995 年 2 月 31 日。如果 date 是 1996 年 1 月 31 日,则由于 1996 年是闰年,返回值是 1996 年 2 月 29 日。
如果计算的日期超前 100 年(减去的年度超过 date 中的年份),就会导致错误发生。
如果 number 不是一个 Long 值,则在计算时取最接近的整数值来计算。
注意 DateAdd 返回值的格式由 Control Panel设置决定,而不是由传递到date 参数的格式决定。
DateAdd ( "m" , 1 , "31-Jan-98")
结果: 28-Feb-98
将日期 31-Jan-98 加上一个月,结果为 28-Feb-98 而非 31-Fe-98 。
DateAdd ( "d" , 20 , "30-Jan-99")
结果: 1999/2/9
将一个日期 30-Jan-99 加上 20 天后的日期。
由于该函数里面只包含一个日期值,那么在查询的时候,就可以不对当前的日期列进行转换,而是将比较的日期及差值放在=、<=、>=的右面,这样,就可以使用索引。

4、最后关于这个系统的建议
最后应这哥们的要求,我们对数据库进行了一番巡检,发现问题还是比较多的
4.1、调整隔离级别!
4.2、内存使用不多,cpu高,io比较大,可能很多物理读,索引优化及碎片整理!
4.3、内存使用上不去,是不是存在sql server支持的内存方面有问题,需要进一步排查!

正文到此结束
Loading...