Why do variables in the WHERE clause cause the execution time to balloon
1
vote
1
answer
79
views
I have a t-sql query that executes hourly, pulling in a variable amount of data depending on the current time when the query executes, i.e.: if it is executing between midnight and 2 AM local time (7 to 9 AM UTC) I want to pull in the last 120 days' worth of data; outside that window I only want to bring in 3 days' worth of data.
While creating the query I tried a few things to avoid having to repeat the query in an IF ELSE statement with hard coded values. As a baseline for testing I created the following query:
SELECT COUNT(*)
FROM dbo.Tickets t
JOIN dbo.TicketsDetails td ON t.ticketGUID = td.ticketGUID
WHERE td.dtCreated > DATEADD(dd, -1, CAST(GETUTCDATE() AS date))
With the hardcoded interval it returns a value of about 750,000 in .829 seconds. When I modify it to use local variables (the second or third WHERE clause below), however, execution time explodes to over 10 minutes:
DECLARE @Interval INT,
@StartDate DATE;
SELECT @Interval = CASE WHEN DATEPART(hh, GETUTCDATE()) IN (7, 8) THEN -120 ELSE -1 END
, @StartDate = DATEADD(dd, @Interval, CAST(GETUTCDATE() AS date));
SELECT COUNT(*)
FROM dbo.Tickets t
JOIN dbo.TicketsDetails td
ON t.ticketGUID = td.ticketGUID
--WHERE td.dtCreated > DATEADD(dd, -1, CAST(GETUTCDATE() AS date))
WHERE td.dtCreated > DATEADD(dd, @Interval, CAST(GETUTCDATE() AS date))
--WHERE td.dtCreated > @StartDate
My question is why does this happen, and if this is working as designed what workarounds are there so I don't have to double the code?
Asked by Jim Stephenson
(11 rep)
Jan 10, 2025, 05:52 PM
Last activity: Jan 11, 2025, 05:56 AM
Last activity: Jan 11, 2025, 05:56 AM