Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««678910»»»

The Cost of Function Use In A Where Clause Expand / Collapse
Author
Message
Posted Friday, February 29, 2008 12:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:46 AM
Points: 5,676, Visits: 6,488
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #462611
Posted Friday, February 29, 2008 12:15 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:46 AM
Points: 5,676, Visits: 6,488
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #462614
Posted Friday, February 29, 2008 12:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:41 PM
Points: 110, Visits: 497
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
Post #462621
Posted Friday, February 29, 2008 12:37 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 9:29 AM
Points: 2,655, Visits: 6,016
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
Post #462627
Posted Friday, February 29, 2008 12:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 30, 2014 4:46 PM
Points: 7, Visits: 90
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
Post #462628
Posted Monday, March 3, 2008 9:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #463218
Posted Monday, March 3, 2008 12:04 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, December 19, 2014 10:31 AM
Points: 436, Visits: 2,317
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
Post #463343
Posted Monday, March 3, 2008 10:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 13, 2008 11:30 PM
Points: 10, 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
Post #463568
Posted Tuesday, March 4, 2008 6:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:59 PM
Points: 35,772, Visits: 32,442
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #463722
Posted Tuesday, March 4, 2008 6:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:41 PM
Points: 110, Visits: 497
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
Post #463732
« Prev Topic | Next Topic »

Add to briefcase «««678910»»»

Permissions Expand / Collapse