SQL Server date index lagging by one day

Question

I have a table with a column dt that I use in a WHERE clause, and another column identifier that is used in a join. Both dt and identifier are indexed.

For all dates before today, the query below runs in less than a second:

select SRDtoday.*
from SRD as SRDtoday
left join (select * from SRD where convert(date, dt) = convert(date, dateadd(day, -1, getdate()))) as SRDyesterday ON (SRDtoday.Identifier = SRDyesterday.Identifier)
where convert(date, SRDtoday.dt) = convert(date, dateadd(day, -1, getdate()))

However, when I run it for today it takes around 15 minutes:

select SRDtoday.*
from SRD as SRDtoday
left join (select * from SRD where convert(date, dt) = convert(date, getdate())) as SRDyesterday ON (SRDtoday.Identifier = SRDyesterday.Identifier)
where convert(date, SRDtoday.dt) = convert(date, getdate())

When I look at the execution plan it looks like before today it is doing an index seek but when I use today in the query it is doing an index scan.

I've tried dropping and rebuilding the index but that doesn't help. Any ideas?

What's the point of the left join? You're do not appear to be using it for anything. — Dave C, Sep 02 '16 at 19:47
sorry, I cleaned up the query a little and stripped the select that uses the join (it does get used). — rlflow, Sep 02 '16 at 19:49

score 1 · Answer 1 · answered Sep 02 '16 at 19:56

You should avoid applying cast/convert or anything of the sort on the fly to an entire column. When you do that, SQL has to apply the conversion to every row whether it is required or not before it can check equality/inequality. Often times this leads to indexes not being used, or not being used properly.

A better approach is to change the conditions to fit the datatype, and not the datatype to fit the conditions.

Ex instead of

WHERE CONVERT(DATE, DT) = '2016-09-02'

You could do:

WHERE DT >= '2016-09-02' AND DT < '2016-09-03'

That said, here is the change I suggest:

declare @myDate DATETIME
set @myDate = convert(datetime,convert(date,getdate()))


select SRDtoday.*
from SRD as SRDtoday
left join (select * 
           from SRD 
           where dt >= @myDate and dt < DATEADD(DD,1,@myDate)) as SRDyesterday 
   ON (SRDtoday.Identifier = SRDyesterday.Identifier)
where SRDtoday.dt >= @myDate and SRDtoday.dt < DATEADD(DD,1,@myDate)

SQL Server date index lagging by one day

1 Answers1