CODEWITHSUNDEEP
sql-servert-sqlindexing
rlflow
rlflow

Reputation: 365

SQL Server date index lagging by one day

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?

Upvotes: 1

Views: 91

Answers (1)

Dave C
Dave C

Reputation: 7402

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)

Upvotes: 1

Related Questions