snippetsqlMinor
dateadd in the where clause - how can I avoid a full scan in this particular situation?
Viewed 0 times
thiscanthefullscanwhereparticularsituationavoidhow
Problem
I have the following query:
Please pay attention to the first line of the where clause again:
Could I change this in a way that my index can get used? The index actually get used, but it is a full scan.
It can also be seen here:
This is my index definition:
This is the full execution plan.
Unfortunately I cannot change the column called
Other than creating a computed column or change the schema, is there anything else that can be done to avoid reading the full table\index scan in this situation?
Update:
After the accepted answer, changed the query accordingly and now the index is used in a seek operation as you can see on the picture below.
Please note the 94% of cost of
SELECT u.userId,
app.applicationId
FROM app.application AS app
INNER JOIN app.applicant AS ap
ON ap.applicantId = app.applicantId
INNER JOIN usr.[user] AS u
ON u.userId = ap.userId
LEFT JOIN msg.emailTemplateSent AS t
ON t.toUserId = u.userId
AND t.emailTemplateName = 'v4_before_sixWeek_latestFlight_reminder'
WHERE Convert(Date, GETUTCDATE()) =
DATEADD(week,-6,Convert(Date, app.flightDateLatest ))
AND t.emailEventId IS NULL
ORDER BY app.applicationId ASCPlease pay attention to the first line of the where clause again:
Convert(Date, GETUTCDATE()) = DATEADD(week,-6,Convert(Date, app.flightDateLatest ))Could I change this in a way that my index can get used? The index actually get used, but it is a full scan.
It can also be seen here:
This is my index definition:
USE [APCore];
CREATE NONCLUSTERED INDEX i_flightDateLatest
ON [app].[application] ( flightDateLatest ASC )
INCLUDE ( [applicantId] , [applicationId] , [programID])
WITH ( PAD_INDEX = OFF,
FILLFACTOR = 100 ,
SORT_IN_TEMPDB = OFF ,
ONLINE = OFF,
--DROP_EXISTING = ON,
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
DATA_COMPRESSION=PAGE,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON )
ON [NONCLUSTERED_INDEXES]This is the full execution plan.
Unfortunately I cannot change the column called
flightDateLatest from datetime to date to avoid convertionOther than creating a computed column or change the schema, is there anything else that can be done to avoid reading the full table\index scan in this situation?
Update:
After the accepted answer, changed the query accordingly and now the index is used in a seek operation as you can see on the picture below.
Please note the 94% of cost of
Solution
You can add 6 weeks to your getutc...
and then test that particular day (flightDateLatest) to be greater or equal to
and less then next day
with output:
and then test that particular day (flightDateLatest) to be greater or equal to
DATEADD(week,6,Convert(Date, GETUTCDATE()))and less then next day
DATEADD(DAY,1,DATEADD(week,6,Convert(Date, GETUTCDATE())))SELECT
*
FROM
(
SELECT CAST('20220511 15:14:52.050' AS DATETIME) as flightDateLatest union all
SELECT CAST('20220512' AS DATETIME)
) as app
WHERE
app.flightDateLatest >= DATEADD(week,6,Convert(Date, GETUTCDATE()))
AND
app.flightDateLatest < DATEADD(DAY,1,DATEADD(week,6,Convert(Date, GETUTCDATE())))with output:
flightDateLatest
2022-05-11 15:14:52.050Code Snippets
SELECT
*
FROM
(
SELECT CAST('20220511 15:14:52.050' AS DATETIME) as flightDateLatest union all
SELECT CAST('20220512' AS DATETIME)
) as app
WHERE
app.flightDateLatest >= DATEADD(week,6,Convert(Date, GETUTCDATE()))
AND
app.flightDateLatest < DATEADD(DAY,1,DATEADD(week,6,Convert(Date, GETUTCDATE())))flightDateLatest
2022-05-11 15:14:52.050Context
StackExchange Database Administrators Q#310332, answer score: 9
Revisions (0)
No revisions yet.