September 15, 2015 at 9:32 am
Lynn Pettis (9/15/2015)
Ed Wagner (9/15/2015)
Eirikur Eiriksson (9/14/2015)
Sorry, slightly hurried and inaccurate😎
CONVERT(DATE,AppDetails.DateDetailDisposed,0) = CONVERT(DATE,DATEADD(DAY,-21,getdate()),0)If your DateDetailDisposed column is a datetime data type, you might run into a performance problem here. Applying a function to a column in the table means that the function has to be applied to every row in the table before filtering out the rows. Using a date range to calculate the date range instead of converting the column in the table is generally a better way to go about it.
AND AppDetails.DateDetailDisposed >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 21, 0)AND AppDetails.DateDetailDisposed < DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 20, 0)
Actually, test it. Doing a CONVERT or CAST on a DateTime value DOES NOT always result in the Optimizer not being able to use an index on that column.
Hmmm...interesting. I did test it and you're right. I've seen CASTs in WHERE clause predicates cause more scans than I even care to count so I avoid them like non-SARGable predicates (or the plague, your choice). However, this is not the case in this case. I threw together the following test, using my physical tally table with 32,000 rows.
if OBJECT_ID('dbo.Dates', 'u') is not null drop table dbo.Dates;
create table dbo.Dates (
ID integer not null identity (1, 1),
constraint Dates_PK primary key (ID),
N Integer not null,
SomeDate datetime not null,
EntryDate datetime not null default getdate());
CREATE NONCLUSTERED INDEX Dates_IDX01 on dbo.Dates(SomeDate);
INSERT INTO dbo.Dates(N, SomeDate)
SELECT t.N, DATEADD(day, t.N, '01/01/1900')
FROM dbo.Tally32K t
SELECT COUNT(*)
FROM dbo.Dates
WHERE CONVERT(Date, SomeDate) > '01/01/1910';
I got an index seek on the NCI. :crazy: Is this because:
1. The index was covering, so it read from the index and then did the CONVERT?
2. The conversion of Datetime to Date is on a list somewhere that I don't know about?
3. The optimizer knows that the conversion only requires that it look at the first integer of the Datetime data type and therefore allows it?
4. Something else I'm not thinking of in my current state of being puzzled?
September 15, 2015 at 9:56 am
ScottPletcher (9/15/2015)
Lynn Pettis (9/15/2015)
Ed Wagner (9/15/2015)
Eirikur Eiriksson (9/14/2015)
Sorry, slightly hurried and inaccurate😎
CONVERT(DATE,AppDetails.DateDetailDisposed,0) = CONVERT(DATE,DATEADD(DAY,-21,getdate()),0)If your DateDetailDisposed column is a datetime data type, you might run into a performance problem here. Applying a function to a column in the table means that the function has to be applied to every row in the table before filtering out the rows. Using a date range to calculate the date range instead of converting the column in the table is generally a better way to go about it.
AND AppDetails.DateDetailDisposed >= DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 21, 0)AND AppDetails.DateDetailDisposed < DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 20, 0)
Actually, test it. Doing a CONVERT or CAST on a DateTime value DOES NOT always result in the Optimizer not being able to use an index on that column.
I'd suggest avoiding any conversion of a table column for comparison unless you absolutely have to, even if for a specific case it works in a given query plan. It's just poor technique.
Miss the point much?? What I was trying to point out is that using a function in a where clause DOES NOT ALWAYS result in a clustered index or table scan.
Corral your horse please.
September 15, 2015 at 9:19 pm
In the case of using the convert function on the column in the where clause, I would only consider it if there are relatively few rows being sought, as those index seeks do come at a cost, roughly 2 times more than without the function.
😎
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply