DateTime Colunm and Indexes

Question

DateTime Colunm and Indexes

brickpack

SSCrazy

Points: 2702
More actions
February 9, 2009 at 5:11 pm

#199791

Hello,
I just found something bazaar with a query that one of my developers wrote. Could someone explain to me what's happening?
The following query causes major overhead. The CPU maxes out and it takes almost 25 minutes to complete the operation.
select col1,col2 into #temptest
from tablea a join tableb on a.id = b.id
where month([date]) = 1
group col1
Running the same query without the month() func takes just over 3 minutes and the CPU hums at a cool 25%.
select col1,col2 into #temptest
from tablea a join tableb on a.id = b.id
where [date] >= '2009-01-01' and [date] < '2009-02-01'
group col1
I do have an index on the [date] column. My question is this. When using the month() func...is the index being used? That's the only reason that I can come up with for the difference in performance.
Thanks
Dave

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 1

[font="Verdana"]Have a look at the query plan. I'd expect it to use the index though.

Also, try and get in the habit of writing your dates as 'YYYYMMDD', because just about every other form is language settings dependent.

[/font]

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

No... when you use any function on the column, the best that will happen is an INDEX SCAN which can actually be worse than a table scan if every row suffers a "BookMark Lookup". The second query you posted is the correct way to do it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 3

[font="Verdana"]True.

Perhaps a better approach would be a join to a Calendar table, where MonthNumber = 1? But if you don't have a Calendar table, your second form is correct.

[/font]

brickpack SSCrazy Points: 2702 More actions · Answer 4

Thanks for the quick replies!

Now I finally know what that "Bookmark Lookup" is in the execution planner.

Dave

Vladan SSC-Insane Points: 22072 More actions · Answer 5

dave b (2/9/2009)
where month([date]) = 1
where [date] >= '20090101' and [date] < '20090201'

As has already been explained, you get best results when working with values that are really present in the column, not manipulated by a function - but there is one more thing to your example.

These conditions are not the same, because MONTH(date) = 1 can be January of any year, not just the current year. Of course it is possible that you only have this year's data in the table, but anyway it isn't correct - it's at least potentially dangerous.