CODEWITHSUNDEEP
sqlsql-serversql-server-2016
Adrian B
Adrian B

Reputation: 41

SQL Server : different execution plan and performance for similar queries

I have 2 queries on the same table and I have expected similar performance, still I get a huge difference - 30 seconds versus ~ 1 second.

Query 1: count users for current month

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())

Query 2: count users for current week

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())

The table has ~ 3.5 million records and it is indexed by RequestDate. The count for the month is doing a full table scan, so it takes 30 seconds, the one for the week is doing RID Lookup in ~ 1 second - this information is coming from the execution plans.

Any idea why the difference?

Upvotes: 0

Views: 51

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

How about trying these queries?

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);

and:

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);

Using functions on columns usually prevents the compiler from using an index. The above assumes that you don't have future request dates.

Upvotes: 3

Related Questions