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



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

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

    https://www.brentozar.com/pastetheplan/?id=HJxAWTZmq .

    Unfortunately I cannot change the column called flightDateLatest from datetime to date to https://dba.stackexchange.com/a/203984/22336

    Other than creating a https://dba.stackexchange.com/a/116348/22336 or change the schema, is there anything else that can be done to avoid reading the https://dba.stackexchange.com/a/177337/22336 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:

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



Suggested Topics

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