Using Date in Where Condition

Question

Using Date in Where Condition

Viewing 7 posts - 31 through 36 (of 36 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Jeff Moden - Saturday, October 7, 2017 9:10 PM
ChrisM@home - Saturday, October 7, 2017 3:25 PM
Phil Parkin - Saturday, October 7, 2017 9:47 AM
aloshya - Saturday, October 7, 2017 9:36 AM
John Mitchell-245523 - Friday, October 6, 2017 10:02 AM
Be careful with that approach. The problem is that the predicate in the WHERE clause becomes non-sargable and therefore if you have an index on the column, it can't be used for a seek operation.
John
So, by Using convert or casr in where clause how does it affect the index column.
It does not affect anything.
But it means that if there is an index on the column, that index cannot be used â€“ leading to an expensive scan rather than (potentially) a seek.
Except - as Lynn pointed out - CASTing DATETIME column to DATE. It's SARGable.
Jeez... after more than 20 years of doing it the other way, I'm probably never going to get used to that exception. I went back and corrected my post above.

It's good advice here, though - first because, as you said earlier, avoiding functions on the column side of the predicate where possible is a good habit to get into, and second because the column in this case is varchar, not datetime.

John

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

John Mitchell-245523 - Monday, October 9, 2017 2:11 AM
Jeff Moden - Saturday, October 7, 2017 9:10 PM
ChrisM@home - Saturday, October 7, 2017 3:25 PM
Phil Parkin - Saturday, October 7, 2017 9:47 AM
aloshya - Saturday, October 7, 2017 9:36 AM
John Mitchell-245523 - Friday, October 6, 2017 10:02 AM
Be careful with that approach. The problem is that the predicate in the WHERE clause becomes non-sargable and therefore if you have an index on the column, it can't be used for a seek operation.
John
So, by Using convert or casr in where clause how does it affect the index column.
It does not affect anything.
But it means that if there is an index on the column, that index cannot be used â€“ leading to an expensive scan rather than (potentially) a seek.
Except - as Lynn pointed out - CASTing DATETIME column to DATE. It's SARGable.
Jeez... after more than 20 years of doing it the other way, I'm probably never going to get used to that exception. I went back and corrected my post above.
It's good advice here, though - first because, as you said earlier, avoiding functions on the column side of the predicate where possible is a good habit to get into, and second because the column in this case is varchar, not datetime.
John

John, the OP changed the column to DATETIME quite early in the thread.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

ChrisM@Work - Monday, October 9, 2017 3:28 AM
John, the OP changed the column to DATETIME quite early in the thread.

Gosh, I'd lost track!

John

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Jeff Moden - Saturday, October 7, 2017 9:10 PM
ChrisM@home - Saturday, October 7, 2017 3:25 PM
Phil Parkin - Saturday, October 7, 2017 9:47 AM
aloshya - Saturday, October 7, 2017 9:36 AM
John Mitchell-245523 - Friday, October 6, 2017 10:02 AM
Be careful with that approach. The problem is that the predicate in the WHERE clause becomes non-sargable and therefore if you have an index on the column, it can't be used for a seek operation.
John
So, by Using convert or casr in where clause how does it affect the index column.
It does not affect anything.
But it means that if there is an index on the column, that index cannot be used â€“ leading to an expensive scan rather than (potentially) a seek.
Except - as Lynn pointed out - CASTing DATETIME column to DATE. It's SARGable.
Jeez... after more than 20 years of doing it the other way, I'm probably never going to get used to that exception. I went back and corrected my post above.

It's worth remembering, not just for this use case, but the other side of the predicate too. Try these on a table with a few million rows, with and without an index on the datetime column:

-- WITHOUT SUPPORTING INDEX
-- 1. Using CAST.
-- Fast: SQL Server can calculate CAST(GETDATE() AS DATE) and gets a good estimate of 149,798,000 rows
SELECT MAX(MyDateTime) FROM dbo.[MyTable] WHERE MyDateTime < CAST(GETDATE() AS DATE) -- 00:00:03

-- 2. Using a parameter.
-- Fast: SQL Server compiles and reuses the plan using this value (parameter sniffing: ParameterRuntimeValue="'2017-10-17'")
DECLARE @TodayDate DATE = CAST(GETDATE() AS DATE)
SELECT MAX(MyDateTime) FROM dbo.[MyTable] WHERE MyDateTime < @TodayDate -- 00:00:03

-- 3. Using a literal.
-- Fast: the estimate of 1 row is close to the actual number of 47 rows
-- The plan is quite different to cases 1 and 2 above.
SELECT MAX(MyDateTime) FROM dbo.[MyTable] WHERE MyDateTime < '20051016' -- 00:00:03

-- 4. Using an expression.
-- Slow: SQL Server can't get anything from the date arithmetic and instead falls back on a default estimate of 1 row.
-- The compiled plan is the same as case 3 above, and it's totally inappropriate for the actual number of 149,797,717 rows.
SELECT MAX(MyDateTime) FROM dbo.[MyTable] WHERE MyDateTime < DATEADD(day,DATEDIFF(day, 0, GETDATE()),0) -- 00:01:25

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

ChrisM@Work - Tuesday, October 17, 2017 5:05 AM
Jeff Moden - Saturday, October 7, 2017 9:10 PM
ChrisM@home - Saturday, October 7, 2017 3:25 PM
Phil Parkin - Saturday, October 7, 2017 9:47 AM
aloshya - Saturday, October 7, 2017 9:36 AM
John Mitchell-245523 - Friday, October 6, 2017 10:02 AM
Be careful with that approach. The problem is that the predicate in the WHERE clause becomes non-sargable and therefore if you have an index on the column, it can't be used for a seek operation.
John
So, by Using convert or casr in where clause how does it affect the index column.
It does not affect anything.
But it means that if there is an index on the column, that index cannot be used â€“ leading to an expensive scan rather than (potentially) a seek.
Except - as Lynn pointed out - CASTing DATETIME column to DATE. It's SARGable.
Jeez... after more than 20 years of doing it the other way, I'm probably never going to get used to that exception. I went back and corrected my post above.
It's worth remembering, not just for this use case, but the other side of the predicate too. Try these on a table with a few million rows, with and without an index on the datetime column:
-- WITHOUT SUPPORTING INDEX -- 1. Using CAST. -- Fast: SQL Server can calculate CAST(GETDATE() AS DATE) and gets a good estimate of 149,798,000 rows SELECT MAX(MyDateTime) FROM dbo.[MyTable] WHERE MyDateTime < CAST(GETDATE() AS DATE) -- 00:00:03

-- 2. Using a parameter.
-- Fast: SQL Server compiles and reuses the plan using this value (parameter sniffing: ParameterRuntimeValue="'2017-10-17'")
DECLARE @TodayDate DATE = CAST(GETDATE() AS DATE)
SELECT MAX(MyDateTime) FROM dbo.[MyTable] WHERE MyDateTime < @TodayDate -- 00:00:03

-- 3. Using a literal.
-- Fast: the estimate of 1 row is close to the actual number of 47 rows
-- The plan is quite different to cases 1 and 2 above.
SELECT MAX(MyDateTime) FROM dbo.[MyTable] WHERE MyDateTime < '20051016' -- 00:00:03

-- 4. Using an expression.
-- Slow: SQL Server can't get anything from the date arithmetic and instead falls back on a default estimate of 1 row.
-- The compiled plan is the same as case 3 above, and it's totally inappropriate for the actual number of 149,797,717 rows.
SELECT MAX(MyDateTime) FROM dbo.[MyTable] WHERE MyDateTime < DATEADD(day,DATEDIFF(day, 0, GETDATE()),0) -- 00:01:25

Thanks for that but I'm pretty sure that those have always worked in a SARGABLE nature because it's the formulas that are made to match the column datatype rather than the other way around. I'll try to remember to test those on the lone remaining 2005 box at work and my 2008 instance at home.

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Jeff Moden - Tuesday, October 17, 2017 5:44 AM
ChrisM@Work - Tuesday, October 17, 2017 5:05 AM
Jeff Moden - Saturday, October 7, 2017 9:10 PM
ChrisM@home - Saturday, October 7, 2017 3:25 PM
Phil Parkin - Saturday, October 7, 2017 9:47 AM
aloshya - Saturday, October 7, 2017 9:36 AM
John Mitchell-245523 - Friday, October 6, 2017 10:02 AM
Be careful with that approach. The problem is that the predicate in the WHERE clause becomes non-sargable and therefore if you have an index on the column, it can't be used for a seek operation.
John
So, by Using convert or casr in where clause how does it affect the index column.
It does not affect anything.
But it means that if there is an index on the column, that index cannot be used â€“ leading to an expensive scan rather than (potentially) a seek.
Except - as Lynn pointed out - CASTing DATETIME column to DATE. It's SARGable.
Jeez... after more than 20 years of doing it the other way, I'm probably never going to get used to that exception. I went back and corrected my post above.
It's worth remembering, not just for this use case, but the other side of the predicate too. Try these on a table with a few million rows, with and without an index on the datetime column:
-- WITHOUT SUPPORTING INDEX -- 1. Using CAST. -- Fast: SQL Server can calculate CAST(GETDATE() AS DATE) and gets a good estimate of 149,798,000 rows SELECT MAX(MyDateTime) FROM dbo.[MyTable] WHERE MyDateTime < CAST(GETDATE() AS DATE) -- 00:00:03

-- 2. Using a parameter.
-- Fast: SQL Server compiles and reuses the plan using this value (parameter sniffing: ParameterRuntimeValue="'2017-10-17'")
DECLARE @TodayDate DATE = CAST(GETDATE() AS DATE)
SELECT MAX(MyDateTime) FROM dbo.[MyTable] WHERE MyDateTime < @TodayDate -- 00:00:03

-- 3. Using a literal.
-- Fast: the estimate of 1 row is close to the actual number of 47 rows
-- The plan is quite different to cases 1 and 2 above.
SELECT MAX(MyDateTime) FROM dbo.[MyTable] WHERE MyDateTime < '20051016' -- 00:00:03

-- 4. Using an expression.
-- Slow: SQL Server can't get anything from the date arithmetic and instead falls back on a default estimate of 1 row.
-- The compiled plan is the same as case 3 above, and it's totally inappropriate for the actual number of 149,797,717 rows.
SELECT MAX(MyDateTime) FROM dbo.[MyTable] WHERE MyDateTime < DATEADD(day,DATEDIFF(day, 0, GETDATE()),0) -- 00:01:25
Thanks for that but I'm pretty sure that those have always worked in a SARGABLE nature because it's the formulas that are made to match the column datatype rather than the other way around. I'll try to remember to test those on the lone remaining 2005 box at work and my 2008 instance at home.

Did you notice the timing for the fourth case, Jeff? It's just as bad if there's an index on the datetime column too. You get a seek, then the remaining rows are read BACKWARD to end of index. So with the index, the first three cases complete in a couple of milliseconds, and the fourth case completes as if the index doesn't exist.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

ChrisM@Work - Tuesday, October 17, 2017 5:50 AM
Did you notice the timing for the fourth case, Jeff? It's just as bad if there's an index on the datetime column too. You get a seek, then the remaining rows are read BACKWARD to end of index. So with the index, the first three cases complete in a couple of milliseconds, and the fourth case completes as if the index doesn't exist.

No... haven't noticed anything, yet... I haven't tested it yet. 😉

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