The Cost of Function Use In A Where Clause

  • GregoryAJackson

    SSCrazy

    Points: 2794

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

    Gregory A Jackson MBA, CSM

  • Heiko Hatzfeld

    SSC Veteran

    Points: 249

    Good Article...

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

  • Anipaul

    SSC-Insane

    Points: 24681

    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.

  • leea

    SSC Enthusiast

    Points: 186

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

  • Leendert van Staalduinen

    Right there with Babe

    Points: 767

    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.

  • w.lengenfelder

    SSC Eights!

    Points: 824

    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]

  • Palwi Chugh

    SSC Journeyman

    Points: 91

    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.

  • leea

    SSC Enthusiast

    Points: 186

    try something along the lines of

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

  • w.lengenfelder

    SSC Eights!

    Points: 824

    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]

  • Palwi Chugh

    SSC Journeyman

    Points: 91

    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

  • Palwi Chugh

    SSC Journeyman

    Points: 91

    Thanks

    I think this'll help me.

  • David Jackson

    SSCertifiable

    Points: 6302

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

  • George H.

    Ten Centuries

    Points: 1382

    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

  • Grant Fritchey

    SSC Guru

    Points: 395227

    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 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • GSquared

    SSC Guru

    Points: 260824

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

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