Click here to monitor SSC
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
Palwi Chugh
Palwi Chugh
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 19
Thanks
I think this'll help me.
David Jackson
David Jackson
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 Visits: 1879
This looks complex but will use your index.

where CreatedDate between dateadd(dd, datediff(dd,0,Getdate()),0) --midnight TODAY
and dateAdd(ss,-1,dateAdd(dd,1,dateadd(dd, datediff(dd,0,Getdate()),0))) --23:59:59 TODAY

HTH

Dave J

Edit: Oops! I see w.lengenfelder posted this already! w00t


http://glossopian.co.uk/
"I don't know what I don't know."
George H.
George H.
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 118
Great article! I'm glad I read it, but now i'm having trouble writing one of my queries...

I need to join 2 tables on smalldatetime columns. One of the tables stores the date and time and the other table doesn't. So table A has '2008-02-27 10:32:31' and table B has '2008-02-27 00:00:00' and I need to join them on table A date = table B date (ignoring the time info).

Normally I use:
...FROM
TableA JOIN TableB
ON Cast(Cast(TableA.DateColumn AS INT) AS Smalldatetime) =
Cast(Cast(TableB.DateColumn AS INT) AS SmallDateTime)
...

Will wrapping these columns in the JOIN clause also cause a degradation of performance like wrapping them in a where clause would?

Is there a more optimized way of writing this?

Thanks,

George
Grant Fritchey
Grant Fritchey
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20733 Visits: 32385
What about using DATEDIFF on days where it equals zero?

Still, joining on something like this is going to be problematic. Most problematic of all, you're joining on columns that clearly have no referential integrity. You can't reliably put these together.

Nice article.

You didn't mention implicit functions, like comparing a string to a datetime or an int to a smallint where SQL Server supplies a function for you even though you didn't write one.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GSquared
GSquared
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16831 Visits: 9729
Good article.

One small quibble with it. In one of the examples, you use "((FullName1 = 'Ed Jones') OR (FullName1 IS NULL))", as an example of how to get Index Seeks, instead of Scans.

In many cases, the moment you put an "or" in a Where clause, you get a scan instead of a seek. Not always, but it is something to watch out for. The same applies for "in ()" (since that's just a way to shorthand multiple "or" statements).

- 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
GSquared
GSquared
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16831 Visits: 9729
George Heinrich (2/28/2008)
Great article! I'm glad I read it, but now i'm having trouble writing one of my queries...

I need to join 2 tables on smalldatetime columns. One of the tables stores the date and time and the other table doesn't. So table A has '2008-02-27 10:32:31' and table B has '2008-02-27 00:00:00' and I need to join them on table A date = table B date (ignoring the time info).

Normally I use:
...FROM
TableA JOIN TableB
ON Cast(Cast(TableA.DateColumn AS INT) AS Smalldatetime) =
Cast(Cast(TableB.DateColumn AS INT) AS SmallDateTime)
...

Will wrapping these columns in the JOIN clause also cause a degradation of performance like wrapping them in a where clause would?

Is there a more optimized way of writing this?

Thanks,

George


Yes, functions in the Join clause have the same effect as functions in the Where clause.

What I generally recommend, if possible, in this case, is add calculated columns to the tables, like so:


alter table TableA
add DateColumnYear as datepart(year, datecolumn),
DateColumnMonth as datepart(month, datecolumn),
DateColumnDay as datepart(day, datecolumn)
go
create index IDX_TableA_DateParts on dbo.TableA (DateColumnDay, DateColumnMonth, DateColumnYear)



Then do your join/where clauses on the calculated columns. Like:


select ...column list...
from dbo.TableA
inner join dbo.TableB
on TableA.DateColumnYear = TableB.DateColumnYear
and TableA.DateColumnMonth = TableB.DateColumnMonth
and TableA.DateColumnDay = TableB.DateColumnB



Alternately, if you don't often select by year, month, day, but do often join on the whole date, you can add a calculated column:


alter dbo.TableA
add DateColumnDate as cast(convert(varchar(25), DateColumn, 102) as datetime)
go
create index IDX_TableA_DateColumnDate on dbo.TableA (DateColumnDate)



Do that to both tables, then join on the calculated column.

In my experience, this is very fast and efficient. Of course, it only applies if you are in a position to add calculated columns to tables.

- 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
RIck Moudy
RIck Moudy
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 13
If you convert your @varDate local to a smalldatetime local before using it in the SELECT statement you can do a straight comparison of the local variable and the table column.

INSTEAD OF:
select * from table_name where dateDiff(d,@varDate,Created_Date) =0

TRY THIS:
DECLARE @sdtDate smalldatetime
SELECT @sdtDate = cast(@varDate AS smalldatetime)
SELECT * FROM table_name WHERE Created_Date = @sdtDate

Bryant McClellan
Bryant McClellan
SSC Eights!
SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)

Group: General Forum Members
Points: 921 Visits: 538
select * from table_name where dateDiff(d,@varDate,Created_Date) =0


It seems to me that this test simply wants to compare an element to a static variable to determine if they match (which is the case if the dateDiff result is 0). Why not convert the variable to the appropriate type and format to match Created_Date (this avoids having CONVERT called explicitly in the WHERE clause by the parser---very expensive) and compare the fields directly. No function wrapped around the real element, so no hiding of the index.

Or is it too early for the caffeine to kick in and I'm missing something very obvious?

Back to the original subject, very good article. I spent a some time myself testing the performance of function calls (intrinsic and UDF) and arrived at the same conclusions. And that is also where I ran into the explicit CONVERT function call in tracing the performance of a WHERE clause.

------------
Buy the ticket, take the ride. -- Hunter S. Thompson
Grant Fritchey
Grant Fritchey
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20733 Visits: 32385
GSquared (2/28/2008)
Good article.

One small quibble with it. In one of the examples, you use "((FullName1 = 'Ed Jones') OR (FullName1 IS NULL))", as an example of how to get Index Seeks, instead of Scans.

In many cases, the moment you put an "or" in a Where clause, you get a scan instead of a seek. Not always, but it is something to watch out for. The same applies for "in ()" (since that's just a way to shorthand multiple "or" statements).


If you really HAVE to do the OR clause, I've found an approach like this works best, to avoid scans as much as possible. For example, if you have one of those stinking reports where the users have demanded 15 different parameters, but they don't know which one they'll use so you have to stack up NULL checks as well as column comparisons:

SELECT...
FROM....
WHERE
CASE WHEN @MyParam IS NULL THEN 1
WHEN @MyParam = table1.Column1 THEN 1
ELSE 0
END = 1



----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
RIck Moudy
RIck Moudy
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 13
I'm curious to know if the 'ON' predicate will behave the same as the 'WHERE' statement in all cases described in the article?
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