SQL Server 2008 – Fix 10+ Minute Query Time with DATEPART in WHERE Clause

sql-server-2008

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?

Best Answer

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.

Related Solutions

How to Avoid Using Variables in SQL Server WHERE Clause

Parameter sniffing is your friend almost all of the time and you should write your queries so that it can be used. Parameter sniffing helps building the plan for you using the parameter values available when the query is compiled. The dark side of parameter sniffing is when the values used when compiling the query is not optimal for the queries to come.

The query in a stored procedure is compiled when the stored procedure is executed, not when the query is executed so the values that SQL Server has to deal with here...

CREATE PROCEDURE WeeklyProc(@endDate DATE)
AS
BEGIN
  DECLARE @startDate DATE = DATEADD(DAY, -6, @endDate)
  SELECT
    -- Stuff
  FROM Sale
  WHERE SaleDate BETWEEN @startDate AND @endDate
END

is a known value for @endDate and an unknown value for @startDate. That will leave SQL Server to guessing on 30% of the rows returned for the filter on @startDate combined with whatever the statistics tells it for @endDate. If you have a big table with a lot of rows that could give you a scan operation where you would benefit most from a seek.

Your wrapper procedure solution makes sure that SQL Server sees the values when DateRangeProc is compiled so it can use known values for both @endDate and @startDate.

Both your dynamic queries leads to the same thing, the values are known at compile-time.

The one with a default null value is a bit special. The values known to SQL Server at compile-time is a known value for @endDate and null for @startDate. Using a null in a between will give you 0 rows but SQL Server always guess at 1 in those cases. That might be a good thing in this case but if you call the stored procedure with a large date interval where a scan would have been the best choice it may end up doing a bunch of seeks.

I left "Use the DATEADD() function directly" to the end of this answer because it is the one I would use and there is something strange with it as well.

First off, SQL Server does not call the function multiple times when it is used in the where clause. DATEADD is considered runtime constant.

And I would think that DATEADD is evaluated when the query is compiled so that you would get a good estimate on the number of rows returned. But it is not so in this case.
SQL Server estimates based on the value in the parameter regardless of what you do with DATEADD (tested on SQL Server 2012) so in your case the estimate will be the number of rows that is registered on @endDate. Why it does that I don't know but it has to do with the use of the datatype DATE. Shift to DATETIME in the stored procedure and the table and the estimate will be accurate, meaning that DATEADD is considered at compile time for DATETIME not for DATE.

So to summarize this rather lengthy answer I would recommend the wrapper procedure solution. It will always allow SQL Server to use the values provided when compiling the the query without the hassle of using dynamic SQL.

PS:

In comments you got two suggestions.

OPTION (OPTIMIZE FOR UNKNOWN) will give you an estimate of 9% of rows returned and OPTION (RECOMPILE) will make SQL Server see the parameter values since the query is recompiled every time.

Sql-server – How to calculate exact month (in days) difference b/w two dates

The problem with your problem (sorry, couldn't resist) is that you haven't properly defined what you expect a month to be. How many months are there between Dec 27 and Feb 28? How about Dec 28 and Feb 28? Dec 29? What about Dec 15 to Jan 13? Dec 15 to Jan 14?

As others have mentioned, DATEDIFF just measures the number of boundaries that have been passed. The following both return 1:

SELECT 
  DATEDIFF(YEAR,'20120101','20131231'), 
  DATEDIFF(YEAR,'20121231','20130101');

Even though you and I both know that only one day has passed in the first example, and almost two years have passed in the second.

If you want everything from before September 4th (since today is February 3rd), then:

DECLARE @d DATE = SYSDATETIME;

SELECT <cols> FROM dbo.tablename
  WHERE VisitDate < DATEADD(DAY, 1, DATEADD(MONTH, -5, @d));

-- the above evaluates to < '2013-09-04'

Best Answer

Related Solutions

How to Avoid Using Variables in SQL Server WHERE Clause

Sql-server – How to calculate exact month (in days) difference b/w two dates

Related Question