Using DateDiff can cause query performance problems in SQL 2005 and 2008
Lately, we have a customer who reported that a query used to run 20 minutes in 2000 now run serveral hours in SQL 2008. After examining the plan, we discovered that the estimates were much more off in SQL 2008. Specifically, the query has a where condition like (DateColumn< DATEADD(mm, DATEDIFF(mm,0,dateadd(month, -6, getdate())))
First of all, if you have a simple expression involving constant and DateAdd like (dateadd(month, -6, '2009-01-01')), SQL Server can optimize it and replace it with actual value like ('2008-07-01'). But if you use a complex expression like above, SQL Server cannot use the same otpimization. Estimates in general can be off.
In this specific scenario, SQL Server 2008/2005, ends up with low estimates. This will impact plan choice. Note that the issue is only happening when you combine datediff with dateadd. If you just use dateadd with getdate (dateadd(month, -6, getdate()), estimate is still reasonable.
If you have any expression like DATEADD(mm, DATEDIFF(mm,0,dateadd(month, -6, getdate()))) , you should manually compute it yourself and then pass it along with your true query.
Instead of doing "select COUNT (*) from t inner join t2 on t.c1 = t2.c2 where c1 < DATEADD(mm, DATEDIFF(mm,0,dateadd(month, -6, getdate())), 0) ", you create a procedure that takes a datetime parameter and then call the procedure and pass the actual value. Here is an example. Another approach is to use EXEC ('sql string') to achieve same effect.
create procedure p_test @begindate datetime
as
select COUNT (*) from t inner join t2 on t.c1 = t2.c2 where c1 < @begindate option (recompile)
go
declare @dt datetime
set @dt =DATEADD(mm, DATEDIFF(mm,0,dateadd(month, -6, getdate())), 0)
exec p_test @dt
After customer took our advice using the procedure, his execution is cut down to 55 seconds (even much faster than 2000's 20-25 minutes).
As a general advice, if you have an expression like above that can be pre-computed, it's better you do so to let sql know the actual value so that it can do a better estimate.
Here is the repro that demonstrate low cardinality estimate in 2005 and 2008
use tempdb
go
create table t (c1 datetime)
go
set nocount on
begin tran
declare @k int
set @k = 0
while @k < 100
begin
declare @i int
set @i = 0
while @i < 365
begin
declare @dt datetime
set @dt = '2008-01-01'
set @dt = DATEADD (dd,@i, @dt)
insert into t values (@dt)
set @i = @i + 1
end
set @k = @k + 1
end
commit tran
go
create index indx on t (c1 )
go
set showplan_all on
go
--for 2008 and 2005, the index seek estimated just 1 row
-- for sql 2000, index seek estimates 10,950 rows
/*
select COUNT (*) from t where c1 <DATEADD(mm, DATEDIFF(mm,0,dateadd(month, -6, getdate())), 0)
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1006],0)))
|--Stream Aggregate(DEFINE:([Expr1006]=Count(*)))
|--Index Seek(OBJECT:([tempdb].[dbo].[t].[indx]), SEEK:([tempdb].[dbo].[t].[c1] < dateadd(month,datediff(month,'1900-01-01 00:00:00.000',dateadd(month,(-6),getdate())),'1900-01-01 00:00:00.000')) ORDERED FORWARD)
*/
select COUNT (*) from t where c1 <DATEADD(mm, DATEDIFF(mm,0,dateadd(month, -6, getdate())), 0)
go
set showplan_all off
go
====================================================
Jack Li | Senior Escalation Engineer | Microsoft SQL Server Support