SQL Server with Mr. Denny

Sep 17 2009   11:00AM GMT

Dates can easily be the hardest datatype to work with.



Posted by: Denny Cherry
Tags:
Index Performance
Index Scan
Index Seek
Indexing
T/SQL

The datetime data type can be one of the hardest to work with when it comes to index optimization.  Most queries that use a datetime data type for filtering (part of the where clause) only want to match the date portion of the value.  Most people handle this via a convert function around the date column.  This causes the index that you create to become useless as the convert function causes the index to be scanned not seeked.

You can see this yourself by creating a table, with a date field, then stuff some records into that table.

CREATE TABLE sample_table
(id INT IDENTITY(1,1) PRIMARY KEY,
dt DATETIME)
go
CREATE INDEX ix_sample_table ON dbo.sample_table
(dt)
go
INSERT INTO sample_table
(dt)
SELECT '1/1/2000 15:00:00'
UNION
SELECT '1/2/2000 12:00:00'
UNION
SELECT '1/3/2000 08:00:00'
go

You can now query the dt column and use the convert function. When you view the execution plan you’ll see that an index scan is being used.

SELECT id, dt
FROM sample_table
WHERE CONVERT(varchar(10), dt, 101) = '01/01/2000'

But it you use the BETWEEN operator instead of the = operator you’ll see the engine using an index seek instead of the index scan.

SELECT id, dt
FROM sample_table
WHERE dt BETWEEN '01/01/2000' AND '01/02/2000'

Now the same thing applies when using other functions to the datetime column in the table. For example if you were to have a system where people need to search based on the timezone where the data is stored in UTC time, but searching needs to happen based on the users time zone.

The easiest way to do this would be to put a dateadd function around the dt field. However even when using the BETWEEN operator you still use an index scan, instead of the seek. In this case I’m searching against the pacific timezone.

SELECT id, dt
FROM sample_table
WHERE dateadd(mi, -420, dt) BETWEEN '01/01/2000' AND '01/02/2000'

Now, if you switch the -420 to 420, and put the DATEADD function around the dates in the BETWEEN operator the index seek will be preserved. The query looks a little strange, but that’s ok. It’s the end result that we are looking for here.

SELECT id, dt
FROM sample_table
WHERE dt BETWEEN dateadd(mi, 420, '01/01/2000') AND dateadd(mi, 420, '01/02/2000')

Now if you have the UtcOffset that you want to use stored in another table such as an Account table. We’ll recreate the sample_table to go with this.

CREATE TABLE sample_table
(id INT IDENTITY(1,1) PRIMARY KEY,
dt DATETIME,
AccountId int)

CREATE TABLE Account
(AccountId INT PRIMARY KEY,
UtcOffset INT)
go
CREATE INDEX ix_sample_table ON dbo.sample_table
(dt)
INCLUDE (AccountId)
go
INSERT INTO sample_table
(dt, AccountId)
SELECT '1/1/2000 15:00:00', 1
UNION
SELECT '1/2/2000 12:00:00', 2
UNION
SELECT '1/3/2000 08:00:00', 1

INSERT INTO Account
SELECT 1, -420
UNION
SELECT 2, -300

In order to make this work, you have to take the UtcOffset value *-1 so that the date is adjusted in the correct direction to make the query work.

SELECT id, dt
FROM sample_table
JOIN Account on sample_table.AccountId = Account.AccountId
WHERE dt BETWEEN dateadd(mi, Account.UtcOffset*-1, '01/01/2000') AND dateadd(mi, Account.UtcOffset*-1, '01/02/2000')

(Ignore the Clustered Index Scan on the Account table, correcting that isn’t the goal of this post.

Hopefully you find this technique useful.

Denny

6  Comments on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • BrianTkatch
    Nice explanation and examples. I'm bookmarking this one to help explain this concept to others.
    0 pointsBadges:
    report
  • Gmmastros
    [B]WHERE dt BETWEEN ‘01/01/2000′ AND ‘01/02/2000′[/B] You need to be careful when using between and dates. That where clause will return rows from the table where the date is 01/02/2000 and the time is midnight. When working with dates, I prefer to use multiple conditions, like this... [B]WHERE dt >= ‘01/01/2000′ AND dt = for the start range and < for the end.
    0 pointsBadges:
    report
  • Gmmastros
    Sorry, that should have been: WHERE dt >= ‘01/01/2000′ AND dt < '01/02/2000'
    0 pointsBadges:
    report
  • Denny Cherry
    This is true, BETWEEN is inclusive, so to use BETWEEN correctly you'd want to use [CODE]WHERE dt BETWEEN ‘01/01/2000′ AND dateadd(ms, -3, ‘01/02/2000′)[/CODE] Unless you were using SQL 2008's new data types [CODE]WHERE dt BETWEEN ‘01/01/2000′ AND dateadd(ns, -1, ‘01/02/2000′)[/CODE]
    65,490 pointsBadges:
    report
  • Sowmi
    In my case i need to compare date portion alone from two different table's datetime fields. I used convert in both sides of the comparision and this join itself resulting in 53 % of the overall cost of the query and results in performance degration. How to tune this query and can we use a syntax similar to this tab1.col1 between tab2.col1 and tab2.col1. Please advise asap. thanks a lot.
    0 pointsBadges:
    report
  • Denny Cherry
    You should be able to do something like this without two much overhead. [CODE]WHERE tab1.col1 BETWEEN CAST(convert(varchar(10, tab2.col1, 101) as datetime) and dateadd(ms, -3, dateadd(dd, 1, cast(convert(varchar(10), tab2.col1, 101) as datetime)))[/CODE] Check my Parens, I think they are correct, but I'm not sure.
    65,490 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: