HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
thiscanthefullscanwhereparticularsituationavoidhow

Problem

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

Solution

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

Code Snippets

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())))
flightDateLatest
2022-05-11 15:14:52.050

Context

StackExchange Database Administrators Q#310332, answer score: 9

Revisions (0)

No revisions yet.