SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Avoid enclosing Indexed Columns in a Function in the WHERE clause
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 09/12/2007 :  09:43:43  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote

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 - 09/12/2007 :  09:56:41  Show Profile  Reply with Quote
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



Go to Top of Page

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 09/24/2007 :  08:07:21  Show Profile  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/24/2007 :  08:32:49  Show Profile  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 09/24/2007 :  13:55:17  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

IanLogan
Starting Member

3 Posts

Posted - 09/27/2007 :  05:02:14  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/27/2007 :  05:40:39  Show Profile  Reply with Quote
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
Go to Top of Page

IanLogan
Starting Member

3 Posts

Posted - 09/27/2007 :  07:13:21  Show Profile  Reply with Quote
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?
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 09/27/2007 :  07:49:20  Show Profile  Visit spirit1's Homepage  Reply with Quote
yes

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

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/27/2007 :  08:37:58  Show Profile  Reply with Quote
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
Go to Top of Page

IanLogan
Starting Member

3 Posts

Posted - 09/27/2007 :  08:58:13  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/27/2007 :  09:26:01  Show Profile  Reply with Quote
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
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 09/27/2007 :  09:57:46  Show Profile  Visit graz's Homepage  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/27/2007 :  10:02:33  Show Profile  Reply with Quote
Although IME Indexed Computed Columns bring their own nightmares with ARITH ABORT settings.

Or is that a thing of the past?
Go to Top of Page

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 09/27/2007 :  10:56:28  Show Profile  Reply with Quote
nope, still a pain in the ...
Go to Top of Page

JD785727
Starting Member

2 Posts

Posted - 09/27/2007 :  14:08:59  Show Profile  Reply with Quote
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)))
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 09/27/2007 :  14:10:28  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

JD785727
Starting Member

2 Posts

Posted - 09/27/2007 :  14:32:17  Show Profile  Reply with Quote
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.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 09/27/2007 :  14:37:32  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/27/2007 :  14:53:44  Show Profile  Reply with Quote
"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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000