Answer the question
In order to leave comments, you need to log in
Why does the query take longer when using variables?
There is a complex query for a large amount of data, which is poured into a table in an already generalized form of several tens of thousands of rows with 7 columns.
When debugging a query in a condition where it is required to specify a date, I explicitly specified the period [date00] between '20190101' and '20190401', launched the script, and it worked out in about two and a half minutes.
But when I switched to using variables, the query execution time increased incomprehensibly
declare @today date = '20190101'
declare @year datetime = (select cast(datename(yyyy, @today) as datetime))
declare @quarter int = datepart(quarter,@today)
declare @qbegin datetime = dateadd(QUARTER,@quarter-1,@year)
declare @qend datetime = dateadd(ss,-1,(dateadd(QUARTER,@quarter,@year)))
select @today,@year,@quarter,@qbegin,@qend
...
...and date00 between @qbegin and @qend--
Answer the question
In order to leave comments, you need to log in
Found on the web:
Performance Impact: Constant value -vs- Variable .
it looks like there are few solutions ...Personal experience: when mastering MS SQL, I noticed that the ADO intermediate layer sometimes changes constants to variables in a real query and vice versa. When using stored procedures, the query plan is built and cached when it is created. This is one of the reasons to wrap the request in a procedure even without additional processing.
the request will start processing without the old cached data
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question