Datediff

  • Does datediff hinders performance in queries.

    I am trying to access the records and it takes almost 40 seconds to filter out of 180000 rows. Which is not much of a data...

    My only filter is where Time_Created < datediff(day,90,getdate())

    Is there a better way to filter for past 90 days

  • Can you post up the execution plan?

    MCITP SQL 2005, MCSA SQL 2012

  • Also is there an index on Time_Created?

  • Do you have an index on Time_Created?

    DATEDIFF will only execute once for the whole query, so it's probable not your problem.

    By the way, you might be wanting to use DATEADD(day, -90, GETDATE()) if you want rows where time_created is previous to 90 days ago.

    SELECT DATEADD(day, -90, GETDATE()) AS dateadd,

    DATEDIFF(day, 90, GETDATE()) AS datediff

    Results:

    dateadd datediff

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

    2015-01-30 12:14:05.363 42032

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (4/30/2015)


    By the way, you might be wanting to use DATEADD(day, -90, GETDATE()) if you want rows where time_created is previous to 90 days ago.

    Good catch!

  • djj (4/30/2015)


    Also is there an index on Time_Created?

    Also, what is the datatype of the Time_Created column? Could be some nasty ol' implicit conversions working here.

    I also agree with Luis.... the formula in the original post really needs to be a DATEADD with a negative value, which may also help with some implicit datatype conversions.

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

  • Changed the signed from < to > which was the requirement though. ANd now it pulled up quick

  • sharonsql2013 (4/30/2015)


    Changed the signed from < to > which was the requirement though. ANd now it pulled up quick

    If you continue to use DATEDIFF like that, you continue to compare integers to dates. I don't know if it'll ever bite you but it's "always" better to do comparisons with the correct datatype whenever you can.

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

  • sharonsql2013 (4/30/2015)


    Does datediff hinders performance in queries.

    I am trying to access the records and it takes almost 40 seconds to filter out of 180000 rows. Which is not much of a data...

    My only filter is where Time_Created < datediff(day,90,getdate())

    Is there a better way to filter for past 90 days

    Quick thought, don't think it's the filter that is the main problem, can you post the actual query / execution plan?

    😎

  • Eirikur Eiriksson (4/30/2015)


    sharonsql2013 (4/30/2015)


    Does datediff hinders performance in queries.

    I am trying to access the records and it takes almost 40 seconds to filter out of 180000 rows. Which is not much of a data...

    My only filter is where Time_Created < datediff(day,90,getdate())

    Is there a better way to filter for past 90 days

    Quick thought, don't think it's the filter that is the main problem, can you post the actual query / execution plan?

    😎

    Op had the relationship backwards and was returning everything but the last 90 days.

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

Viewing 10 posts - 1 through 9 (of 9 total)

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