Sample Header Ad - 728x90

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