SQL Server :相似查询的不同执行计划和性能

Question

我在同一个表上有两个查询，我希望有类似的性能，但我仍然得到了巨大的差异- 30秒与~1秒。

查询一:统计当月用户数

SELECT datepart(year,getdate()) as TheYear, 
       DATEPART(month, GETDATE()) AS TheMonth, 
       COUNT(DISTINCT Username) AS TheUsers
FROM dbo.[AP-Data] 
WHERE datepart(year,RequestDate) =  datepart(year,getdate()) 
  AND datepart(month,RequestDate) = datepart(month,getdate())

查询2:统计当前周的用户数

SELECT datepart(year,getdate()) as TheYear, 
       DATEPART(week, GETDATE()) AS TheWeek, 
       COUNT(DISTINCT Username) AS TheUsers
FROM dbo.[AP-Data] 
WHERE datepart(year,RequestDate) =  datepart(year,getdate()) 
  AND datepart(week,RequestDate) = datepart(week,getdate())

该表有大约350万条记录，并由RequestDate编制索引。当月的计数是执行全表扫描，所以它需要30秒，一周的计数是在~1秒内执行RID查找-此信息来自执行计划。

你知道为什么会有这样的区别吗？

Answer 1

试试这些查询怎么样？

SELECT year(getdate()) as TheYear, month(GETDATE()) AS TheMonth,
       COUNT(DISTINCT Username) AS TheUsers
FROM dbo.[AP-Data]
WHERE RequestDate >= dateadd(month, datediff(month, 0, getdate()), 0);

和：

SELECT year(getdate()) as TheYear, month(GETDATE()) AS TheMonth,
       COUNT(DISTINCT Username) AS TheUsers
FROM dbo.[AP-Data]
WHERE RequestDate >= dateadd(week, datediff(week, 0, getdate()), 0);

在列上使用函数通常会阻止编译器使用索引。以上假设您没有未来的请求日期。