All we need is an easy explanation of the problem, so here it is.
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:
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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 ) );
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂