The Cost of Function Use In A Where Clause

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Gregory Jackson » The Cost of Function Use In A Where Clause

99 posts, Page 1 of 10

1 2 3 4 5 »»»

The Cost of Function Use In A Where Clause

Rate Topic

Display Mode

Topic Options

Author

Message

GregoryAJackson

Posted Wednesday, February 27, 2008 11:19 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 26, 2013 2:11 PM
Points: 108, Visits: 485

Comments posted to this topic are about the item The Cost of Function Use In A Where Clause

Gregory A Jackson MBA, CSM

Post #461426

Heiko Hatzfeld

Posted Thursday, February 28, 2008 2:47 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, November 25, 2008 9:48 AM
Points: 57, Visits: 8

Good Article...

I often see querries who do this wrong... Especially the "Date-30" querries.

Post #461506

Anipaul

Posted Thursday, February 28, 2008 3:01 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 2:50 AM
Points: 4,785, Visits: 1,334

Good article.

Mostly we ignore this when we write T-SQL. That time our main concern is the right solution (mostly). Thanx for a good article.

Post #461514

leea

Posted Thursday, February 28, 2008 3:12 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, March 07, 2012 2:08 AM
Points: 10, Visits: 34

Good article, I guess the same applies to the use of case statements in the where clause which the performance hit has caused us to rewrite a few SPs!!!

Post #461519

Leendert van Staalduinen

Posted Thursday, February 28, 2008 3:30 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, February 09, 2012 4:42 AM
Points: 29, Visits: 396

I found this a very understandable article. Assuming that everything that has been written is correct (and I have no reason to doubt that), this is very valuable information for me: it gives some understanding, plus practical, everyday examples with solutions. What else does someone wants to know ?
I am eager to read other people's comments.
Suggestion: see also the very valuable SSC article "TSQL LAB 2 - Writing Conditional WHERE Clauses", posted on February 27, 2008.
Leendert.

Post #461522

w.lengenfelder

Posted Thursday, February 28, 2008 3:40 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 19, 2013 7:20 AM
Points: 131, Visits: 521

Thanks for this good Article

If you wont use time data for searching you need to cut off the time information.
The query can look like:

WHERE PlacedOnQueue <= DateAdd(mm,-30,DateAdd(dd,DateDiff(dd,0,GetDate()),0))

Post #461527

Palwi Chugh

Posted Thursday, February 28, 2008 4:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 23, 2010 10:54 PM
Points: 5, Visits: 19

Hi,
I have table with one of its column(Created_Date) as smalldatetime, having index created on it.
Now I have to query this table to get all records with created_date equal to some specific date. I am using following query for this

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

But this query doesn't seems to be optimized as its not making any use of index created on Created_Date.

Can any one help me on this.

Post #461539

leea

Posted Thursday, February 28, 2008 4:42 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, March 07, 2012 2:08 AM
Points: 10, Visits: 34

try something along the lines of

select * from table_name where convert(varchar(11), Created_Date, 106) = convert(varchar(11), @varDate, 106)

Post #461541

w.lengenfelder

Posted Thursday, February 28, 2008 4:46 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 19, 2013 7:20 AM
Points: 131, Visits: 521

you can also try this

SELECT *
FROM table_name
WHERE Created_Date BETWEEN
DATEADD(d,DATEDIFF(d,0,@varDate),0)
AND DATEADD(ms, -3,DATEADD(d,DATEDIFF(d,0,@varDate)+1,0))

Post #461542

Palwi Chugh

Posted Thursday, February 28, 2008 4:46 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 23, 2010 10:54 PM
Points: 5, Visits: 19

Well this too will work, but my problem is still same if use convert in where clause it'll not make use of index and thus performane issue remains the same

Post #461543

« Prev Topic | Next Topic »

99 posts, Page 1 of 10

1 2 3 4 5 »»»

Permissions