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

by

The 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.                      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.

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

It can also be seen here:

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

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 tableindex 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:

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

The Solutions:

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.

Method 1

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

Method 2

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


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment