Article: Avoid enclosing Indexed Columns in a Function in the WHERE clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Avoid enclosing Indexed Columns in a Function in the WHERE clause

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-09-12 : 09:43:43

When a function is wrapped around an indexed column in the WHERE clause it will usually prevent the proper usage of that index. This article gives an example and discusses what other options are available.

Read Avoid enclosing Indexed Columns in a Function in the WHERE clause

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-12 : 09:56:41

HEY!

Who is this guy, AskSQLTeam?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2007-09-24 : 08:07:21

Isn't your main objection to functions in general however?
Surely it makes no difference whether the underlying column is indexed or not. You still have the same problem of a performance hit that is inherent to functions of this sort.

Kristen
Test

22859 Posts

Posted - 2007-09-24 : 08:32:49

Nah, I don't think that is the point.

YEAR(MyDateColumn) can always be recoded in the style of:
MyDateColumn >= '01-Jan-2007' AND MyDateColumn < '01-Jan-2008'

I see lots of things where DEVs have coded a manipulation of a column to then match a parameter, rather than manipulating the parameter to match the column!

SUBSTRING(MyColumn, 3, 3) = @PARAM

rather than

MyColumn LIKE '___' + @PARAM + '%'

(not that that is likely to use an index, anyway, but you get my drift I'm sure!)

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-24 : 13:55:17

quote:
Originally posted by coolerbob

Isn't your main objection to functions in general however?
Surely it makes no difference whether the underlying column is indexed or not. You still have the same problem of a performance hit that is inherent to functions of this sort.

No, it is pretty much like he says -- don't wrap indexed columns in functions if you can avoid it. Otherwise, in any other place, of course they are fine to use, especially when you need them.

- Jeff
http://weblogs.sqlteam.com/JeffS

IanLogan
Starting Member

3 Posts

Posted - 2007-09-27 : 05:02:14

I used to use BETWEEN for date comparisons but then found problems with the time element: if the first column value is say '05/30/2007 14:30:10' then the BETWEEN misses the values in the morning. So I have moved to using DATEDIFF with the d - day qualifier. Will this fall into the same problem then as using YEAR, etc?

Ian Logan

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 05:40:39

If you use the formula on the Column itself then yes. So:


WHERE DATEDIFF(Day, MyDate, GetDate()) = 0
or
WHERE YEAR(MyDate) = 2007

won't use any index for MyDate. However:


WHERE     MyDate >= DATEADD(Day, -1, GetDate())
      AND MyDate < DATEADD(Day, 1, GetDate())

should use the index.

(That is displayed for simplicity of reading, to actually have a range from Midnight-to-midnight, ignoring any Time value in MyDate, you would need:


WHERE     MyDate >= DATEADD(Day, DATEDIFF(Day, 0, GetDate())-1, 0)
      AND MyDate < DATEADD(Day, DATEDIFF(Day, 0, GetDate())+1, 0)
or
WHERE     MyDate >= DATEADD(Year, DATEDIFF(Year, 0, GetDate())-1, 0)
      AND MyDate < DATEADD(Year, DATEDIFF(Year, 0, GetDate())+1, 0)

Kristen

IanLogan
Starting Member

3 Posts

Posted - 2007-09-27 : 07:13:21

I have actually used the trick of adding/subtracting a day from the date criteria, as you have shown, but then changed to the DateDiff.
Original way was:
SET @DateTo = DATEADD(d, 1, GETDATE())
SET @DateFrom = GETDATE()
...
WHERE MyDate >= @DateFrom AND MyDate < @DateTo

Current way is:
WHERE DateDiff(d, MyDate, GETDATE()) = 0

So I should revert to the original way for speed then?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-27 : 07:49:20

yes

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 08:37:58

Or even:


WHERE MyDate >= GETDATE() AND MyDate < DATEADD(d, 1, GETDATE())

if it potentially matters to you [which probably equates to you having tables with large numbers of rows] you should also consider the impact of Parameter Sniffing, which may make pre-calculating @DateFrom and @DateTo preferable to having an in-line formula such as this. But it may go the other way. Only a Test could establish what the actual result will be.

But other way you should not use the


WHERE DateDiff(d, MyDate, GETDATE()) = 0

form.

Kristen

IanLogan
Starting Member

3 Posts

Posted - 2007-09-27 : 08:58:13

OK, so what about the question of comparison between dates that are indexed (because they are needed in the WHERE), for example:

WHERE DateDiff(d, MyDate1, MyDate2) < 0
i.e where MyDate1 is later than MyDate2

What is the best way to do this without using Functions? My problem here is that the dates also include time and I am wanting Day comparison only.
Ian Logan

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 09:26:01

If you've got two columns to compare, based on then being within a day of each other, you will have to use functions.

If its important for performance then work on optimising some other criteria, and see if:


WHERE     MyDate1 >= DATEADD(Day, DATEDIFF(Day, 0, MyDate2), 0)
      AND MyDate1 <  DATEADD(Day, DATEDIFF(Day, 0, MyDate2)+1, 0)

performs better.

And if its still an issue duplicate the storage of MyDate1 and MyDate2 in their respective tables as just-dates so that you can do a straight equality comparison between them.

I still think its crap that SQL Server doesn't have specific DATE [only] and TIME datatypes in 2005!

Kristen

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2007-09-27 : 09:57:46

Don't forget you can always store an indexed date only computed column and use that: http://www.sqlteam.com/article/using-indexed-computed-columns-to-improve-performance

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 10:02:33

Although IME Indexed Computed Columns bring their own nightmares with ARITH ABORT settings.

Or is that a thing of the past?

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2007-09-27 : 10:56:28

nope, still a pain in the ...

JD785727
Starting Member

2 Posts

Posted - 2007-09-27 : 14:08:59

You can also convert the datetime to the range by doing the below. You can do something similar creating a stored procedure to be called by another procedure that will convert your date range parameters.

MYDATE >= CONVERT(DATETIME, (CONVERT(VARCHAR(10), @DATE1, 121))
AND MyDate <= DATEADD(ms, -1, (CONVERT(DATETIME, (CONVERT(VARCHAR(10), (DATEADD(dd, 1, @DATE1)), 121)))

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-27 : 14:10:28

so you convert a datetime to varchar and then back to datetime??
why?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

JD785727
Starting Member

2 Posts

Posted - 2007-09-27 : 14:32:17

You don't realy need to convert back to date time especially if you use it in a parameter conversion stored procedure you could just have your parameter be datetime. But the main reason is to set the hour to 11:59:999 so you get everything datetime stamped in the given time frame. Just another option. Using the >= then just the < works to.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-27 : 14:37:32

ever thought of doing this:

MYDATE >= DATEADD(d, DATEDIFF(d, 0, @DATE1), 0)
AND MyDate < DATEADD(d, DATEDIFF(d, 0, @DATE1), 1)

faster, nicer, cleaner

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 14:53:44

"MYDATE >= CONVERT(DATETIME, (CONVERT(VARCHAR(10), @DATE1, 121))"

IME "massaging" dates by converting via String is slow, relative to native date manipulation.

Although

DATEADD(Day, DATEDIFF(Day, 0, SomeDate), 0)

looks pretty ghastly, it has the best performance of any "remote the time" method that I have seen - so far!

See http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=38940#120953

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources