Any performance difference using constant values ​​compared to parameters?

Question

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

An example query with a constant value in where:

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

Proposed (improved?) Request with declared parameters:

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

Is there any difference? I look at plans for doing something similar to the two above requests, and I see no difference. However, it seems that I remember that if you use constant values ​​in SQL statements, the SQL server will not reuse the same query execution plans or something like that, which will lead to poor performance, but is this true?

Answer 1

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

Addressing variables, which is SQL in question, when compiling a special series, SQL Server compiles each statement within itself. Therefore, when compiling a query with a variable, it does not return to check for any purpose, so it compiles an execution plan optimized for an unknown variable. At the first start, 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 this particular value, so you can create a more optimal plan, but with an added cost of recompilation.

To specifically answer your question:

However, it seems that I remember that if you use constant values ​​in SQL statements, the SQL server will not reuse the same query execution plans or something like that, which will lead to poor performance, but is this true?

Yes, it is true that the same plan cannot be reused for different constant values, but this does not necessarily result in poor performance. Perhaps a more suitable plan may be used for this particular constant (for example, choosing to search by index bookmarks for sparse data), and this change in the query plan may outweigh the cost of recompilation. So almost always it comes to SQL performance issues. The answer depends on .

For parameters, the default behavior is that the execution plan is compiled based on when the parameters (parameters) are used, when the procedure or function is executed for the first time.

I answered similar questions before going into more detail with examples that cover a lot of things, therefore, rather than repeating various aspects of this, I will simply relate the questions:

• Does binding the input parameters of a stored procedure to local variables help optimize the query?
• Provide a cold cache when starting a request
• Why does SQL Server use index scanning instead of index search when the WHERE clause contains parameterized values

Answer 2

So many things are involved in your question, and all this is related to statistics.

SQL compiles the execution plan for even Adhoc requests and stores them in the plan cache for reuse if they are considered 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: each time we try to use different literals, so sql saves the plan if it considers it safe. You can see that the second query scores are the same as litla 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 question:
Passing the local variable as param, let's use the same value 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 in the screenshot below, the use of local variables is estimated to be less than the approximate 29.5 lines that are associated with the statistics.

output:

Thus, statistics are crucial when choosing a query plan (nested loops or performing a scan or search), from the examples, you can see how the ratings are different for each method. Next from the perspective of planning a tablet plan

You may also wonder what happens if I pass many adhoc requests, as SQL generates a new plan for the same request, even if there is a change in space, below are links that 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 the local variable does not match the parameter.

Assuming the column is indexed or has statistics, SQL Server uses a statistical histogram to calculate an estimate of the number of qualification rows based on the provided constant value. The request will also be automatically parameterized and cached if it is trivial (issue the same plan regardless of values) so that subsequent executions avoid the cost of compiling the requests.

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

With a local variable, SQL Server uses the total power of statistics to create a plan because the actual value is not known at compile time. This plan may be good for some values, but suboptimal for others when the request is not trivial.