10+ minute query time with DATEPART in WHERE clause

Question

I have a list of records with datetimes over more than 1 year. My goal is to compare the count of today's records with the count of records from last year, where the week # and day of the week from last year's search match today's week # and day of the week.

For example: if today is 07-26-2016, then the week number is 31, and the day is Tuesday (3). Then I would be searching for week number 31, Tuesday (3) of 2015. This day is 07-28-2015.

My problem is that, if I use a WHERE clause with the date in form '07-28-2015', the query completes in ~30 seconds. But if I use a WHERE clause with DATEPARTs for year, week, and day of week, my query takes > 10 minutes.

Here are the two queries to compare:

first, with the full date in the WHERE clause. This takes 30 seconds

SELECT COUNT(*)
FROM my_table
WHERE CAST(my_datetime AS DATE) = '2015-07-28'

now, with the DATEPARTs. This takes 10+ minutes

SELECT COUNT(*)
FROM my_table
WHERE DATEPART(yy,CAST(my_datetime AS DATE)) = 2015
  AND DATEPART(wk,CAST(my_datetime AS DATE)) = 31
  AND DATEPART(dw,CAST(my_datetime AS DATE)) = 3

I have also tried the second query without casting my_datetime as DATE, and it is just as slow. They DO work, and I have compared the results from the two, but it is necessary that I use the second query so that I can replace the static values (2015, 31, 3) with

DATEPART(yy,GETDATE())-1 / DATEPART(yy,CAST(GETDATE() AS DATE))-1
DATEPART(wk,GETDATE()) / DATEPART(wk,CAST(GETDATE() AS DATE))
DATEPART(dw,GETDATE()) / DATEPART(dw,CAST(GETDATE() AS DATE))

That way, the query will always give me results for last year's corresponding date.

How do I optimize this query to be faster? Or am I approaching this incorrectly?

Kenneth Fisher · Answer 1 · 2016-07-26T19:43:41.220

Why not try using a calendar table? Using the code to create one (took about 1 second) from here.

SELECT COUNT(1)
FROM My_Table
JOIN #Dim
    ON My_Table.my_datetime = #Dim.date
WHERE DATEPART(year,CAST(getdate() AS DATE))-1 = [year]
  AND DATEPART(week,CAST(getdate() AS DATE)) = [week]
  AND DATEPART(weekday,CAST(getdate() AS DATE)) = [DayOfWeek]

In general (as I'm sure has been mentioned in the comments by now) any time you use functions on a column in the WHERE clause you are going to take a performance hit. Frequently a very large one. Among other things those columns are not SARGable (search argument, ie can use an index).

ypercubeᵀᴹ · Answer 2 · 2016-07-26T19:58:40.147

In short, you want to use this type of condition for the query to be efficient:

SELECT COUNT(*)
FROM my_table
WHERE my_datetime >= '2015-07-28' 
  AND my_datetime < '2015-07-29' ;

Now the only problem is to find the date values of the "same" day, last year. Since the weekday and week of a day of the year is a rather complicated calculation, I would try basing the calculations on the week and weekday of January 1st:

First, to find January 1st of previous year ('20010101' is an arbitrary date):

SET @prev_year_Jan_01 = DATEADD(year, 
                                DATEDIFF(year, '20010101', GETDATE()) - 1, 
                                '20010101') ;

Then we can use the week and weekday parts of the wanted date, to find the "same" day last year:

SET @week_1 = 1 ;    -- Jan 01 is always week 1
SET @weekday_1 = DATEPART(weekday, @prev_year_Jan_01) ;

SET @weeks = DATEPART(week, GETDATE()) - @week_1 ;   -- weeks pasted since Jan 01
SET @weekdays = DATEPART(weekday, GETDATE()) - @weekday_1 ;

and finally:

SET @last_year_this_day = DATEADD(day, @weekdays,     -- add day diff
                            DATEADD(week, @weeks,     -- and week diff
                              @last_year_Jan_01)) ;

Then we can use:

SELECT COUNT(*)
FROM my_table
WHERE my_datetime >= @last_year_this_day 
  AND my_datetime < DATEADD(day, 1, @last_year_this_day) ;

SQLDevDBA · Accepted Answer · 2016-07-26T21:01:25.210

I like Kenneth Fisher's response.

If you don't have the option of creating the calendar table, I think you should looks for the exact date first, set that date as a variable, then use the variable in your WHERE clause.

Building the date takes less than a second, then you have a direct filter.

Took a little while to get the calculations right, but I think this works.

DECLARE @DateToSearch DATETIME;
SET @DateToSearch =  CAST('01/01/' + CAST((YEAR(GETDATE())-1) AS VARCHAR) AS DATETIME); --Set Variable to 01/01/LastYear
SET @DateToSearch = DATEADD(WEEK, DATEPART(WEEK,GETDATE())-1, @DateToSearch); --Set Variable to 01/01/Last year + Weeks
SET @DateToSearch = DATEADD(WEEK,DATEDIFF(WEEK,0,@DateToSearch),0); --Set Variable to Floor of week
SET @DateToSearch = DATEADD(DAY, DATEPART(dw,GETDATE())-2, @DateToSearch); --Add Current Day of Week
SELECT @DateToSearch;

SELECT COUNT(*) 
FROM my_table
WHERE CAST(my_datetime AS DATE) = @DateToSearch

Today's date "match" for last year returns: 7/28/2015.

10+ minute query time with DATEPART in WHERE clause

3 Answers3