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. 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:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
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
Another potential solution is adding a computed column to your table and indexing that instead, but that’s probably overkill here. It makes more sense in situations when your date math is based on two columns.
ALTER TABLE app.application ADD flightDateLatestComputed AS DATEADD ( WEEK, -6, CONVERT ( date, flightDateLatest, 112 ) );