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

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.

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

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

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.

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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 Reply