Able to optimize date comparision? 1

[Qik3Coder]

I need to return all records created in a specific YYYY-MM
I've optimized the query by filtering out the IDs prior to joining all my other tables, but I'm curious if there's a better date compare.

Here's the pertinant code bit:

FROM(
	SELECT LCi.ID FROM dbo.myTable LCi (NOLOCK)
	WHERE datepart(year,(LCi.myDate)) =  (datepart(year, @myDate))
	AND datepart(month,(LCi.myDate)) =  (datepart(month, @myDate))
	  ) as LC

TIA,
Lodlaiden

A lack of experience doesn't prevent you from doing a good job.

 

[markros]

The best way is to avoid doing any calculations on the date column.

where mydate >= dateadd(month, datediff(month, '19000101',@myDate),'19000101') and myDate < dateadd(month, datediff(month, '19000101', @myDate),'19000201')

Take a look at this blog

Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code


PluralSight Learning Library

 

[gmmastros]

Markros's advice is spot on because it makes the query sargable. However, if there isn't a suitable index on the table it probably won't help much.

In this particular query, I would suggest the you make sure there is an index on myDate and id.

You can see what your indexes look like by running this:

sp_helpindex 'LCI'

Check to see if there is an index that has myDate as the first column and id as the second column. If not, add it and then you will see a performance improvement.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Qik3Coder]

gmmastros,
SSMS 2k8r2 is sweet, I ran the Include Actual Execution and it showed me where I was missing an index. (This is the first time i've touched this particular database)

mmarkos' suggestion took my query
from:
old/new
62%/38%
to:
66%/34%

The query is not complex. It's straight joins on ID's with some SUM and COUNT functions. The date comparision is the only real logic. The only reason I asked was because of the date posts I have seen the last couple days.

I learned 2 things today.
1.) The query engine does prefer the fixed value on the left (I had inferred this from experience)
2.) The whole sargable comparision and how to convert an index scan to an index seek. I could get it sometimes on my own, but i've had several that I couldn't figure out.

Thanks Guys!
Lodlaiden


A lack of experience doesn't prevent you from doing a good job.