dateadd in the where clause - how can I avoid a full scan in this particular situation?

Question

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]

This is the full execution plan.

Unfortunately I cannot change the column called flightDateLatest from datetime to date to avoid convertion

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 the original query to the 6% improved query:

Sabin B · Accepted Answer · 2022-03-30T12:13:27.540

You can add 6 weeks to your getutc...
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.050

score 2 · Answer 2 · answered Mar 30 '22 at 12:44

computed

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
            )
        );

dateadd in the where clause - how can I avoid a full scan in this particular situation?

2 Answers2

computed