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
flightDateLatest from datetime to date to avoid convertion
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: