Date strange Behaviour

  • All,


    Hope someone can advise, We have a reporting tool  where a developer sharing me script it generates a query which runs faster when running with Where  DATE > '2021-01-01 00:00:00.000'  but it goes drastically slow when change to DATE > '2022-01-01 00:00:00.000'. Apparently that expected to be faster as with 2022 its only last 4 months data but its not and it takes (X6) more runtime. I ran the sql script directly in SMMS and its doing the same , I have already tried out indexes suggested by SQL but nothing really seems help. >2021 runs and 50 seconds change it to >2022 and it takes 6+ minutes .

    Really appreciate if anyone can suggest a solution.


  • Is that the only change to the script? What reporting tool?

    Can you provide DDL for the tables including indexes? See How to Post Performance Problems

    Get an actual execution plan & share it so the experts here can analyze. You can generate it in SSMS or  Sentry One Plan Explorer, and can either upload the .sqlplan file (it's XML) or share via Paste the Plan.

  • Most likely, SQL detected that fewer rows would be returned and therefore changed how it would access those tables based on row estimates.  If the row estimates are off, SQL can produce a lesser-performing plan.

    As ratbak noted, we would need to see additional details to provide a more detailed response.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • To go along with what Scott stated, when is the last time you rebuilt statistics on the date column?  How many rows have been changed according to the modification_counter column in sys.dm_db_stats_properties ( )?

    I also recommend that you post the rest of the query because you may have other issues with it.

    Last but not lease, your criteria needs to have a ">=" rather than just a ">".

    --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)
    Intro to Tally Tables and Functions

Viewing 4 posts - 1 through 3 (of 3 total)

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