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 Wednesday, February 27, 2008 11:19 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
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
Posted Thursday, February 28, 2008 2:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Thursday, February 28, 2008 3:01 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:46 AM
Points: 5,403, Visits: 1,400
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
Posted Thursday, February 28, 2008 3:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 7, 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
Posted Thursday, February 28, 2008 3:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 17, 2013 1:19 PM
Points: 29, Visits: 397
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
Posted Thursday, February 28, 2008 3:40 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 5:31 AM
Points: 132, Visits: 532
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
Posted Thursday, February 28, 2008 4:30 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
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
Posted Thursday, February 28, 2008 4:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 7, 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
Posted Thursday, February 28, 2008 4:46 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 5:31 AM
Points: 132, Visits: 532
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
Posted Thursday, February 28, 2008 4:46 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
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 »

Add to briefcase 12345»»»

Permissions Expand / Collapse