dateadd in the where clause - how can I avoid a full scan in this particular situation?
I have the following query:
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 ASC
Please 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]
Unfortunately I cannot change the column called
flightDateLatestfrom datetime to date to https://dba.stackexchange.com/a/203984/22336
Other than creating a https://dba.stackexchange.com/a/116348/22336 or change the schema, is there anything else that can be done to avoid reading the https://dba.stackexchange.com/a/177337/22336 in this situation?
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 the original query to the 6% improved query:
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
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.050