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 ««12345»»»

The Cost of Function Use In A Where Clause Expand / Collapse
Author
Message
Posted Thursday, February 28, 2008 4:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 23, 2010 10:54 PM
Points: 5, Visits: 19
Thanks
I think this'll help me.
Post #461548
Posted Thursday, February 28, 2008 4:54 AM


SSC-Addicted

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

Group: General Forum Members
Last Login: Saturday, July 12, 2014 6:44 AM
Points: 441, Visits: 1,799
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!



http://glossopian.co.uk/
"I don't know what I don't know."
Post #461549
Posted Thursday, February 28, 2008 5:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, May 4, 2013 5:21 AM
Points: 52, Visits: 117
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
Post #461564
Posted Thursday, February 28, 2008 5:43 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 15,517, Visits: 27,893
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #461572
Posted Thursday, February 28, 2008 6:02 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #461578
Posted Thursday, February 28, 2008 6:12 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #461586
Posted Thursday, February 28, 2008 6:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 28, 2008 7:06 PM
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
Post #461589
Posted Thursday, February 28, 2008 6:16 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:09 AM
Points: 846, Visits: 471
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
Post #461591
Posted Thursday, February 28, 2008 6:20 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 15,517, Visits: 27,893
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #461592
Posted Thursday, February 28, 2008 6:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 28, 2008 7:06 PM
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?
Post #461596
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse