Why SQL Server Go Slow When Using Variables

Why SQL Server go slow when using variables?

It's because when the values are hard coded, it can look up the statistics it has on the data in the table, and figure out the best query to run. Have a look at the execution plans of each of these queries. It must be scanning when your using variables.

if the range is always small you might be able to use an index hint to help this.

Slow sql statement when using variables

The problem is that the query optimizer does a bad job on finding a suitable index when using variables. This is a known issue.

If you use EXPLAIN on both queries, you will see the difference. Just try to avoid variables when not necessary.

For the first query, the optimizer "sees" the chosen values and decides an index can be perfectly used to satisfy the selected range more efficiently.

For the second query, the optimizer is unaware of the two values that define the range, and decides to fall back to a FULL SCAN instead.

Variables make query performance worse

SELECT * FROM [DIME_WH].[dbo].[FactOrderLines2] FL (nolock)
WHERE DD_OrderDate >= '2018-08-01'
  AND DD_OrderDate <= '2018-08-17'

When constant is used in parameter, then Optimiser create special plan for this query.so if same query is executed with same value then plan is reuse, if value is change then another plan is created.

So Parameter with constant value is fast.

SELECT *
FROM [DIME_WH].[dbo].[FactOrderLines2] FL (nolock)
WHERE DD_OrderDate >= @StartDate
  AND DD_OrderDate <= @EndDate

When variable is use in parameter.Then Optimizer create Execution plan for the First parameter value that was passed .

For Example @StartDate='2018-08-01' and @EndDate='2018-08-07' value were pass for first time.
Then optimal execution plan is created by optimiser. This plan is good enough for this value.
Next Time @StartDate='2018-08-01' and @EndDate='2018-08-31' value is pass then same previous plan is use which may not be optimal for this parameter.

In another word same plan which was Optimal for first value is Sub optimal for another value.

so query may perform poor and slow.This is known as Parameter sniffing.

There are several ways to overcome this problem.

Parameter Sniffing

Note : In this thread we are only focussing on why variable performance is slow while other factor remaining constant.

Why is a T-SQL variable comparison slower than GETDATE() function-based comparison?

After doing a lot of reading and researching, I've discovered the issue here is parameter sniffing. Sql Server attempts to determine how best to use indexes based on the where clause, but in this case it isnt doing a very good job.

See the examples below :

Slow version:

declare @dNow DateTime  
Select @dNow=GetDate()  
Select *  
From response_master_Incident rmi  
Where rmi.response_date between DateAdd(hh,-2,@dNow) AND @dNow  

Fast version:

Select *  
From response_master_Incident rmi  
Where rmi.response_date between DateAdd(hh,-2,GetDate()) AND GetDate()  

The "Fast" version runs around 10x faster than the slow version. The Response_Date field is indexed and is a DateTime type.

The solution is to tell Sql Server how best to optimise the query. Modifying the example as follows to include the OPTIMIZE option resulted in it using the same execution plan as the "Fast Version". The OPTMIZE option here explicitly tells sql server to treat the local @dNow variable as a date (as if declaring it as DateTime wasnt enough :s )

Care should be taken when doing this however because in more complicated WHERE clauses you could end up making the query perform worse than Sql Server's own optimisations.

declare @dNow DateTime

SET @dNow=GetDate()

Select ID, response_date, call_back_phone 
from response_master_Incident rmi
where rmi.response_date between DateAdd(hh,-2,@dNow) AND @dNow

-- The optimizer does not know too much about the variable so assumes to should perform a clusterd index scann (on the clustered index ID) - this is slow

-- This hint tells the optimzer that the variable is indeed a datetime in this format (why it does not know that already who knows)
OPTION(OPTIMIZE FOR (@dNow = '99991231'));

SQL query with lots of IN parameters is slow

In Performance perspective IN clause is not good, try some thing below like this

DECLARE @Tmp TABLE(Id INT)
INSERT INTO @Tmp(Id) VALUES(@param1), (@param2), (@param3), (@param4), (@param5)

SELECT 
   [time_taken], [distance], [from_location_geocode_id],
   [to_location_geocode_id] 
FROM 
[Travel_Matrix] 
WHERE 
EXISTS (SELECT 1 FROM @Tmp Where @Tmp.Id=[from_location_geocode_id])
AND EXISTS (SELECT 1 FROM @Tmp Where @Tmp.Id=[to_location_geocode_id])

Slow query caused by parameter variables, but why?

A summary of what lead to a solution:

Add a covering index on supporterid, callEnd.

The assumption here is that the optimizer can use this index (in contrast with callEnd, supporterid) to

• first join tblSupporterMainDetailsand tblCallLogs
• further use it in the where clause for selecting callEnd

Add the option OPTION(RECOMPILE)

all cudo's to TiborK and Hunchback for explaining the difference to the optimizer of using hard coded constants or variables.

Performance Impact - Constant value -vs- Variable

When you use the constant, the value is known to the optimizer so it
can determine selectivity (and possible index usage) based on that.
When you use a variable, the value is unknown to the optimizer (so it
have to go by some hardwired value or possibly density info). So,
technically, this isn't parameter sniffing, but whatever article you
find on that subject should also explain the difference between a
constant and a variable. Using OPTION(RECOMPILE) will actually turn
the variabe to a parameter sniffing situation.

In essence, there is a big difference between a constant, a variable
and a paramater (whcih can be sniffed).

sql like query slow if using declare parameter but fast if not

I would imagine that you must have a non covering index with leading column comparepnfwd that is used by the literal query but not by the query with the variable.

You can use OPTION (RECOMPILE) to get SQL Server to recompile the plan taking into account the actual variable value.

---

Related Topics