Comments posted to this topic are about the item The Cost of Function Use In A Where Clause
Gregory A Jackson MBA, CSM
I often see querries who do this wrong... Especially the "Date-30" querries.
Mostly we ignore this when we write T-SQL. That time our main concern is the right solution (mostly). Thanx for a good article.
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!!!
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.
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:
[font="Courier New"]WHERE PlacedOnQueue <= DateAdd(mm,-30,DateAdd(dd,DateDiff(dd,0,GetDate()),0)) [/font]
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.
try something along the lines of
select * from table_name where convert(varchar(11), Created_Date, 106) = convert(varchar(11), @varDate, 106)
you can also try this
WHERE Created_Date BETWEEN
AND DATEADD(ms, -3,DATEADD(d,DATEDIFF(d,0,@varDate)+1,0))
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
I think this'll help me.
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
Edit: Oops! I see w.lengenfelder posted this already! :w00t:
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:
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?
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.
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 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
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
Viewing 15 posts - 1 through 15 (of 98 total)