'Why does option recompile make this query run ~10 times faster (even after sp_updatestats)

I have a relatively simple query, I have an event table and I want to query all records with a date within the last 12 to next 12 hours. The table has ~1 million rows.

When I run the query with option (recompile) it takes around 25ms, without option (recompile) it takes around 250ms. I have run sp_updatestats and it did not make a difference.

Declare
@EventDateTimeHoursBack int = 12,
@EventDateTimeHoursForward int = 12

Declare
    @BackTime datetime = DATEADD(HOUR, -@EventDateTimeHoursBack, GETUTCDATE()),
    @ForwardTime datetime = DATEADD(HOUR, @EventDateTimeHoursForward, GETUTCDATE())

Select
        *
    From
        dbo.Event 
    Where
        EventDateUtc >= @BackTime
        and EventDateUtc <= @ForwardTime
    Option (Recompile)

This is the query plan with option recompile

With option recompile query plan

This is the query plan without option recompile

Without option recompile query plan



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source