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

  • I have the following query:

    SELECT      u.userId,
    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.

    enter image description here

    It can also be seen here:

    enter image description here

    This is my index definition:

    USE [APCore];
    CREATE NONCLUSTERED INDEX i_flightDateLatest  
    ON [app].[application] ( flightDateLatest  ASC  )  
    INCLUDE ( [applicantId] , [applicationId] , [programID])  
            FILLFACTOR = 100  , 
            SORT_IN_TEMPDB = OFF , 
            ONLINE = OFF, 
            IGNORE_DUP_KEY = OFF, 
           ALLOW_ROW_LOCKS = ON, 
           ALLOW_PAGE_LOCKS = ON ) 

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

    Other than creating a or change the schema, is there anything else that can be done to avoid reading the in this situation?


    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:

    enter image description here

  • 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 CAST('20220511 15:14:52.050' AS DATETIME) as flightDateLatest union all
        SELECT CAST('20220512' AS DATETIME) 
    ) as app
        app.flightDateLatest >= DATEADD(week,6,Convert(Date, GETUTCDATE()))
        app.flightDateLatest < DATEADD(DAY,1,DATEADD(week,6,Convert(Date, GETUTCDATE())))

    with output:

    2022-05-11 15:14:52.050

Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2