Date strange Behaviour

Question

Post reply

Date strange Behaviour

SQLEYES

Old Hand

Points: 374
More actions
April 29, 2022 at 8:52 am

#4024117

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.

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

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

ratbak Ten Centuries Points: 1333 More actions · Answer 1

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.

ScottPletcher SSC Guru Points: 100948 More actions · Answer 2

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) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

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 ( https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-stats-properties-transact-sql )?

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)