SQL - any performance difference using constant values vs parameters?

Question

Is there any difference, with regards to performance, when there are many queries running with (different) constant values inside a where clause, as opposed to having a query with declared parameters on top, where instead the parameter value is changing?

Sample query with with constant value in where clause:

select
*
from [table]
where [guid_field] = '00000000-0000-0000-000000000000' --value changes

Proposed (improved?) query with declared parameters:

declare @var uniqueidentifier = '00000000-0000-0000-000000000000' --value changes

select
*
from [table]
where [guid_field] = @var

Is there any difference? I'm looking at the execution plans of something similar to the two above queries and I don't see any difference. However, I seem to recall that if you use constant values in SQL statements that SQL server won't reuse the same query execution plans, or something to that effect that causes worse performance -- but is that actually true?

Answer 1

It is important to distinguish between parameters and variables here. Parameters are passed to procedures and functions, variables are declared.

Addressing variables, which is what the SQL in the question has, when compiling an ad-hoc batch, SQL Server compiles each statement within it's own right. So when compiling the query with a variable it does not go back to check any assignment, so it will compile an execution plan optimised for an unknown variable. On first run, this execution plan will be added to the plan cache, then future executions can, and will reuse this cache for all variable values.

When you pass a constant the query is compiled based on that specific value, so can create a more optimum plan, but with the added cost of recompilation.

So to specifically answer your question:

However, I seem to recall that if you use constant values in SQL statements that SQL server won't reuse the same query execution plans, or something to that effect that causes worse performance -- but is that actually true?

Yes it is true that the same plan cannot be reused for different constant values, but that does not necessarily cause worse performance. It is possible that a more appropriate plan can be used for that particular constant (e.g. choosing bookmark lookup over index scan for sparse data), and this query plan change may outweigh the cost of recompilation. So as is almost always the case regarding SQL performance questions. The answer is it depends.

For parameters, the default behaviour is that the execution plan is compiled based on when the parameter(s) used when the procedure or function is first executed.

I have answered similar questions before in much more detail with examples, that cover a lot of the above, so rather than repeat various aspects of it I will just link the questions:

• Does assigning stored procedure input parameters to local variables help optimize the query?
• Ensure cold cache when running query
• Why is SQL Server using index scan instead of index seek when WHERE clause contains parameterized values

Answer 2

There are so many things involved in your question and all has to do with statistics..

SQL compiles execution plan for even Adhoc queries and stores them in plan cache for Reuse,if they are deemed safe.

select * into test from sys.objects

select schema_id,count(*) from test
group by schema_id


--schema_id 1 has 15
--4 has 44 rows

First ask: we are trying a different literal every time,so sql saves the plan if it deems as safe..You can see second query estimates are same as literla 4,since SQL saved the plan for 4

--lets clear cache first--not for prod
dbcc freeproccache

select * from test
where schema_id=4

output:

select * from test where 
 schema_id=1

output:

second ask :
Passing local variable as param,lets use same value of 4

--lets pass 4 which we know has 44 rows,estimates are 44 whem we used literals
declare @id int
set @id=4
select * from test

As you can see below screenshot,using local variables estimated less some rough 29.5 rows which has to do with statistics ..

output:

So in summary ,statistics are crucial in choosing query plan(nested loops or doing a scan or seek) ,from the examples,you can see how estimates are different for each method.further from a plan cache bloat perspective

You might also wonder ,what happens if i pass many adhoc queries,since SQL generates a new plan for same query even if there is change in space,below are the links which will help you

Further readings:
http://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/
http://sqlperformance.com/2012/11/t-sql-queries/ten-common-threats-to-execution-plan-quality

Answer 3

First, note that a local variable is not the same as a parameter.

Assuming the column is indexed or has statistics, SQL Server uses the statistics histogram to glean an estimate the qualifying row count based on the constant value supplied. The query will also be auto-parameterized and cached if it is trivial (yield the same plan regardless of values) so that subsequent executions avoid query compilation costs.

A parameterized query also generates a plan using the stats histogram with the initially supplied parameter value. The plan is cached and reused for subsequent executions regardless of whether or not it is trivial.

With a local variable, SQL Server uses the overall statistics cardinality to generate the plan because the actual value is unknown at compile time. This plan may be good for some values but suboptimal for others when the query is not trivial.