Z
Z
zhaar2019-04-26 14:13:06
SQL Server
zhaar, 2019-04-26 14:13:06

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--

The [date00] column, by which the filtering takes place, has the datetime format
At first I thought that the script might be working for a long time due to the initial discrepancy in data formats (one of the variables was just date), but no, I brought everything to a single look, but the query works out longer than with explicit dates. Why?
Yes, there is a workaround for this problem in using dynamic sql, but it's still not clear why the variables affect the script execution time so much.

Answer the question

In order to leave comments, you need to log in

2 answer(s)
K
Konstantin Tsvetkov, 2019-04-26
@zhaar

Found on the web:
Performance Impact: Constant value -vs- Variable .

it looks like there are few solutions ...
the request will start processing without the old cached data
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.

Z
zhaar, 2019-04-26
@zhaar

For those who are too lazy to read, the solution is to add the line
OPTION(recompile) to the end of a heavy request.
And the request will start processing without the old cached data. Peace, work. May.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question