Post reply

The Cost of Function Use In A Where Clause

  • February 27, 2008 at 11:19 pm

    #71583

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

    Gregory A Jackson MBA, CSM

  • February 28, 2008 at 2:47 am

    #783055

    Good Article...

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

  • February 28, 2008 at 3:01 am

    #783063

    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.

  • February 28, 2008 at 3:12 am

    #783066

    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!!!

  • February 28, 2008 at 3:30 am

    #783069

    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.

  • February 28, 2008 at 3:40 am

    #783074

    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]

  • February 28, 2008 at 4:30 am

    #783082

    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.

  • February 28, 2008 at 4:42 am

    #783083

    try something along the lines of

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

  • February 28, 2008 at 4:46 am

    #783084

    you can also try this

    [font="Courier New"]

    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))

    [/font]

  • February 28, 2008 at 4:46 am

    #783085

    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

  • February 28, 2008 at 4:53 am

    #783090

    Thanks

    I think this'll help me.

  • February 28, 2008 at 4:54 am

    #783091

    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."

  • February 28, 2008 at 5:18 am

    #783099

    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

  • February 28, 2008 at 5:43 am

    #783104

    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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 28, 2008 at 6:02 am

    #783108

    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

Viewing 15 posts - 1 through 15 (of 98 total)

You must be logged in to reply to this topic. Login to reply