SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Cost of Function Use In A Where Clause


The Cost of Function Use In A Where Clause

Author
Message
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14488 Visits: 8989
Okay, but we're still running under the assumption that @StartDate and @EndDate are constants set by the coder/end user. What if they're dynamic?

I have a situation where I actually use variables to compare later table dates with, but the variables are based on the current date & time verses values in a table.

tblAccountingPeriods has an ID field (int identity), a StartDate and an EndDate, both datetime. We use this table for all our month end stuff, since our month end doesn't necessarily coincide with calendar month end.


Declare @LastMonthEnd datetime;

If Day(GetDate()) > 26 and Day(GetDate()) <= 31
Set @LastMonthEnd = (Select Max(EndDate) from tblAccountingPeriods
where DateAdd(mm,-1,GetDate()) > BeginDate
and Month(DateAdd(mm,-1,GetDate())) = Month(EndDate))
Else
Set @LastMonthEnd = (Select Max(EndDate) from tblAccountingPeriods
where DateAdd(mm,-2,GetDate()) > BeginDate
and Month(DateAdd(mm,-2,GetDate())) = Month(EndDate));



I use this code to set the variables so that in the SELECT later on, I can say "WHERE FieldX = @LastMonthEnd".

The point is, we're talking about ways to improve functionality on the main SELECT statement's WHERE clause, but if you have a situation where your dates are dynamic and you have to get them from a source to create your variables to begin with, you're still going to eventually have to use a function in a WHERE clause at some point. Even if it's not in the actual query, but in the setting of the variable as above.

So, unless I'm wrong, there are just some situations where you have to determine the best place to put the function and then bite the bullet and accept the processing cost.

Thoughts?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14488 Visits: 8989
FYI, I know I need to reverse the BeginDate in the above code with the DateAdd. But it was when I got to the AND part of the WHERE that I realized I would still have a function on both sides.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
GregoryAJackson
GregoryAJackson
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 506
I agree with you Brandie,

The Point is, I guess, is to try hard to reduce or limit the use of functions in a WHERE clause when and where you can.

If you cant, you cant.

same concept as using Temp Tables and cursors.

Reduce them as much as possible but sometimes you gotta do what you gotta do.

As DBAs, Architects, Engineers, we just need to have a good understanding of the ramifications of our decisions. BUT,.....at some point the rubber hits the road and we gotta make a choice. Sometimes the choices arent ideal.


Great discussions here.


GAJ

Gregory A Jackson MBA, CSM
Luke L
Luke L
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3940 Visits: 6127
Brandie,

Any chance something like this would work for you?

If Day(GetDate()) > 26 and Day(GetDate()) <= 31
Set @LastMonthEnd =
(Select Max(EndDate)
from tblAccountingPeriods
where BeginDate < DateAdd(mm,-1,GetDate())
and endDate BETWEEN DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)
AND DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
Else
Set @LastMonthEnd = (Select Max(EndDate) from tblAccountingPeriods
where BeginDate < DateAdd(mm,-2,GetDate())
and EndDate BETWEEN DATEADD(mm, DATEDIFF(mm,0,getdate())-2, 0)
AND DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0))




To help us help you read this

For better help with performance problems please read this
ruben ruvalcaba
ruben ruvalcaba
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 104
Thanks to all,

As some of you suggested, I been using a computed column for month and year and doing the queries over this columns, but I was looking for an alternative, because I don't know if this approach could be faster than another alternative. I'll try with the solution proposed by SSChasing Mays and SSC Rookie and compare the performance.

Regards
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23351 Visits: 9730
I did some tests on the use of IsNull in the Where clause and found the following:

If the column being tested allows nulls, it will use an index or table scan (depending on whether there's an index available, of course), even if there are no nulls in the column. If the column does not allow nulls, SQL server ignores the isnull function and uses an index seek.

This made me wonder if a check constraint on a column would have similar behavior.


create table dbo.CKTest (
Date datetime primary key)
go
insert into dbo.CKTest (date)
select distinct date
from common.dbo.dates
where datepart(year, date) = 2007



Then I ran:


select date
from dbo.CKTest
where datepart(year, date) = 2007



Got a clustered index scan (of course).

Then added:


alter table dbo.CKTest
add constraint CK_Date_2007 check(datepart(year, date) = 2007)



Ran the same select. Still a clustered index scan. Not a seek.

So there's a slightly different mechanism in place for making SQL ignore IsNull vs DatePart, even when it could know before-hand what the result is going to be.

Good to know on the IsNull thing. Would never have thought of checking that, since I'd never run IsNull on a non-nullable column. Not sure what practical use it is, but definitely interesting to know.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
mstjean
mstjean
Mr or Mrs. 500
Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)

Group: General Forum Members
Points: 561 Visits: 2546
ruben ruvalcaba (2/29/2008)
Hi,

First of all thank you, it's a good point to be considered. In fact, I use functions on the WHERE clause very often, specially when filtering dates ... how do you optimize a query like this:

SELECT SUM(Amount) FROM myEntries WHERE YEAR(myEntries.myDate) = @Year AND MONTH(myEntries.myDate) <= @Month

Any clue? thank you

regards


Not positive nobody's replied yet... and just doing this off the cuff,...
Assuming @year and @month are parms...

Instead of @year-- declare @gedate and init as jan 1 of whatever @year is...
Instead of @month, declare @ltdate and init as the first of the month AFTER @month...

SO let's say you had @year=2008 and @month = 3

Set @gedate=1/1/2008 and @ltdate=4/1/2008. (easily derived from old parms)

Your WHERE turns to:

...WHERE myentries.mydate >= @gtdate AND myentries.mydate < @ltdate



EDIT
Ahh... as it turns out, I am third or fourth on the "response" list to this one. One thing I do prefer (and I *thought* I'd benchmarked it to confirm it causes no additional overhead) is to use

...WHERE foo >= @gedate and foo < @ltdate

rather than

...WHERE foo BETWEEN (@d1 and @d2) -- or is that "IS BETWEEN"; I never use this construct anyway...

I like to use the appropriately named GEdate (greater or equal) and LTdate (lessthan) so I don't get messed up based on the time portion of a (small)datetime. In environments I've worked in, sometimes we will zero out the time portion of a (small)datetime-- (Expiration Dates); in others (Phone Call Taken) we do not. If I write a routine I can use the logic above without worrying about whether a date is 3/3/2008 00:00:00 or 3/3/2008 23:59:59.890


Cursors are useful if you don't know SQL
Rishi Girdhar
Rishi Girdhar
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 23
Greg, a very nice and useful article.I understand the demerit of using functions in 'Where' clause.

Do/does you/anybody have any idea/s on usage of function in 'Select' clause OR in 'JOINS' (User defined tabular functions)on the performance of a query?

Thanks in advance

Rishi
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85346 Visits: 41078
It's certainly no Panacea, but the following article has some insight as to why you can have performance problems with some UDF's in the SELECT list...

http://www.sqlservercentral.com/articles/Test+Data/61572/

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GregoryAJackson
GregoryAJackson
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 506
function use in the SELECT List and or on the JOIN can also cause severe problems (maybe even worse then in the WHERE Clause).

by placing a Function in the select, that function will execute 1x per row.

We had these at my work also when I first got there. They were causing terrible performance system wide.

Removing them all helped immensely. typically you can replace the functions with some other mechanism of retrieving the data needed (Derived tables, correlated subQueries, etc)

you really need to pay close attention to the Query Plan and specifically to the SubTree cost of the plan. If the SubTree cost approaches 10.0 (in my experience), you need to consider modifying the query.

Cheers,

GAJ

Gregory A Jackson MBA, CSM
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search