Date Causing Problem

Question

Date Causing Problem

r5d4

SSCrazy

Points: 2522
More actions
September 14, 2017 at 3:25 am

#323506

I've got an interesting one with a date in a query.
The first where clause in the query runs in 30 seconds
WHERE datecolumn between '20170701' and '20170731'
When I pass the dates as parameters it runs in 30 seconds too.
Change the clause to this and it takes several minutes...
WHERE datecolumn between DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-2,0) and DATEADD(MONTH,DATEDIFF(MONTH,-1,GETDATE())-2,-1)
When I compare execution plans in the same batch, the DATEADD version is 33% versus 67% when providing the dates, yet it still runs slower.
Any thoughts appreciated.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 1

r5d4 - Thursday, September 14, 2017 3:25 AM
I've got an interesting one with a date in a query.
The first where clause in the query runs in 30 seconds
WHERE datecolumn between '20170701' and '20170731'
When I pass the dates as parameters it runs in 30 seconds too.
Change the clause to this and it takes several minutes...
WHERE datecolumn between DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-2,0) and DATEADD(MONTH,DATEDIFF(MONTH,-1,GETDATE())-2,-1)
When I compare execution plans in the same batch, the DATEADD version is 33% versus 67% when providing the dates, yet it still runs slower.
Any thoughts appreciated.
Thanks

"SARGability" and date functions is generally not good... See Rob Farley's article SARGable functions in SQL Server for some discussion.

Thomas Rushton
blog: https://thelonedba.wordpress.com

r5d4 SSCrazy Points: 2522 More actions · Answer 2

Ah, I thought I'd avoided Sargability by not manipulating my date column, only the GETDATE() function.
Thanks for that,
Rich

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

r5d4 - Thursday, September 14, 2017 4:08 AM
Ah, I thought I'd avoided Sargability by not manipulating my date column, only the GETDATE() function.
Thanks for that,
Rich

You DID avoid problems with SARGability because you didn't include a column in the formulas in the WHERE clause. The problem may be that the system doesn't know the values of those at compile or run time, especially since GETDATE() is normally considered to be indeterminate.

Add an OPTION (RECOMPILE) to that query and see if that fixes things. If it were a high hit-rate GUI call used thousands of times per hour, I'd suggest one of the other slightly more complicated methods that you can find on the Internet.

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

ScottPletcher SSC Guru Points: 101120 More actions · Answer 4

As an aside, I suggest you use >= and < for all date/time/datetime comparisons rather than between. That also gets rid of the need for the "tricky" use of -1 for the date:

WHERE datecolumn >= '20170701' and datecolumn < '20170801'
--Note the pain here trying to code a between accurately for the month of February!

WHERE datecolumn >= DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-2,0) and
datecolumn < DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-1,0)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

ScottPletcher - Thursday, September 14, 2017 3:31 PM
As an aside, I suggest you use >= and < for all date/time/datetime comparisons rather than between. That also gets rid of the need for the "tricky" use of -1 for the date:
WHERE datecolumn >= '20170701' and datecolumn < '20170801'
--Note the pain here trying to code the between accurately for the month of February!
WHERE datecolumn >= DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-2,0) and
datecolumn < DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-1,0)

Amen to that!

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