The Cost of Function Use In A Where Clause

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

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

  • Sorry Michaell,

    I dont understand the question here...

    GAJ

    Gregory A Jackson MBA, CSM

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


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

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


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

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


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

  • 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

  • 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

  • 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

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

  • Thanks Grant,

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

    Thanks again,

    Rishi

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

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