T-SQL Select from view much slower with variable

Question

I have a view that runs fast (< 1s) when specifying a value in the where clause:

SELECT *
FROM vwPayments
WHERE AccountId = 8155

...but runs slow (~3s) when that value is a variable:

DECLARE @AccountId BIGINT = 8155

SELECT *
FROM vwPayments
WHERE AccountId = @AccountId

Why is the execution plan different for the second query? Why is it running so much slower?

Answer 1

In the first case the parameter value was known while compiling the statement. The optimizer used the statistics histogram to generate the best plan for that particular parameter value.

When you defined the local variable, SQL server was not able to use the parameter value to find 'the optimal value'. Since the parameter value is unknown at compile time, the optimizer calculates an estimated number of rows based on 'uniform distribution'. The optimizer came up with a plan that would be 'good enough' for any possible input parameter value.

Another interesting article that almost exactly describes your case can be found here.

Answer 2

In short the statistical analysis the query optimizer uses to pick the best plan picks a seek when the value is a known value and it can leverage statistics and a scan when the value is not known. It picks a scan in the second choice because the plan is compiled before the value of the where clause is known.

While I rarely recommend bossing the query analyzer around in this specific case you can use a forceseek hint or other query hints to override the engine. Be aware however, that finding a way to get an optimal plan with the engine's help is a MUCH better solution.

I did a quick Google and found a decent article that goes into the concept of local variables affecting query plans more deeply.

Answer 3

DECLARE @Local_AccountId BIGINT = @AccountId 

SELECT *
FROM vwPayments
WHERE AccountId = @Local_AccountId 
OPTION(RECOMPILE)

It works for me