Post reply

Datediff

  • April 30, 2015 at 9:53 am

    #300221

    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

  • April 30, 2015 at 10:08 am

    #1794770

    Can you post up the execution plan?

    MCITP SQL 2005, MCSA SQL 2012

  • April 30, 2015 at 10:10 am

    #1794772

    Also is there an index on Time_Created?

  • April 30, 2015 at 10:14 am

    #1794773

    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

  • April 30, 2015 at 10:18 am

    #1794775

    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!

  • April 30, 2015 at 10:57 am

    #1794794

    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)

  • April 30, 2015 at 11:18 am

    #1794800

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

  • April 30, 2015 at 1:57 pm

    #1794837

    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)

  • April 30, 2015 at 8:42 pm

    #1794889

    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?

    😎

  • May 1, 2015 at 6:47 am

    #1794959

    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