'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
This is the query plan without option recompile
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|


