The Cost of Function Use In A Where Clause

  • Michaell

    Grasshopper

    Points: 17

    Would it be the time itself is pretty much an unique value already?

    So it don't produce a good index on it?

  • GregoryAJackson

    SSCrazy

    Points: 2794

    Sorry Michaell,

    I dont understand the question here...

    GAJ

    Gregory A Jackson MBA, CSM

  • Jeff Moden

    SSC Guru

    Points: 996676

    Michaell (3/5/2008)


    Would it be the time itself is pretty much an unique value already?

    So it don't produce a good index on it?

    Absolutely not... the more unique a value it is, the better an index can be made from it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 996676

    Just a test... please ignore...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 996676

    Just another test... please ignore.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Rishi Girdhar

    SSC Veteran

    Points: 200

    Greg,

    How can we improve the following query?

    -------------------------

    --------------------------

    WHERE

    filter 1

    AND filter 2

    AND CONVERT(DATETIME,A.DateValue) BETWEEN (GetDate()-30) AND (GetDate()+30)

    Column DateValue in table A has datatype (varchar/nvarchar) but datetime.

    Thanks in advance.

    Rishi

  • Rishi Girdhar

    SSC Veteran

    Points: 200

    Many times we need to use function 'MAX' in 'Select' clause specially for dates. Does anyone has a thought on different approach.

    For example

    select max(a.closedate) as closedate, a.offerid

    from a inner join b on a.id =b.id

    inner join c on b.xx = c.xx

    group by a.offerid

    Thanks,

    Rishi

  • Grant Fritchey

    SSC Guru

    Points: 396564

    Rishi Girdhar (3/6/2008)


    Many times we need to use function 'MAX' in 'Select' clause specially for dates. Does anyone has a thought on different approach.

    For example

    select max(a.closedate) as closedate, a.offerid

    from a inner join b on a.id =b.id

    inner join c on b.xx = c.xx

    group by a.offerid

    Thanks,

    Rishi

    We did a series of tests and found, depending on how you do your indexing, TOP actually works a bit better than MAX. You have to include an ORDER BY and get the ASC/DESC right.

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

  • Rishi Girdhar

    SSC Veteran

    Points: 200

    Thanks Grant,

    I will try with TOP and ORDER BY and see the difference.

    Thanks again,

    Rishi

Viewing 9 posts - 91 through 99 (of 99 total)

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