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支持的内存方面有问题，需要进一步排查！

原文连接：

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29371470/viewspace-2130906/，如需转载，请注明出处，否则将追究法律责任。

---

本人补充内容：

首先说明一下，如果 使用 DateAdd 方法能够解决问题，下面的内容就不需要看了（根据你查询数据的实际情况）。如果没办法使用dateAdd方法完成我们想要实现的功能，那么要考虑使用其他函数了（我这里使用的是 sqlserver中的 convert 函数进行处理的）

最近在测试系统的时候，发现系统有些统计功能非常慢，界面功能有按年、月、季度等选项来统计数据，最后找到sql语句发现没有走索引，但是表中创建了索引以及表数据才 10万条数据量。

查询条件是这样的（这里是按月统计数据）：

where datediff(month, upload_time, getdate()) = 0 

发现没有走索引，数据库表中为 upload_time 字段创建了索引。于是对sql语句进行整改。

整改后的查询条件是这样的（查询10月份数据）：

where convert(varchar(7), upload_time, 23) = '2020-10'

关于 convert 及其他日期函数详细信息，请查看下面的内容：

SqlServer数据类型及日期格式转换参照表

SQL Date 函数( MySQL 与 SQL SERVER)