Parameterization (ii): How queries are executed

In the previous article I covered the execution plan cache and the process through which the batch process was performed. This article will use a few of the most common examples of how queries are executed in several ways.

Take a look at this query I'm using:

Selectid, Name, lastpurchasedatefrommarketing.customerswherecountry = N ' IL ';

This is a simple search for a customer in a given country. Now let's test the previous query and show seven different ways to query it. It also describes the impact of execution methods on plan caching and plan reuse.

To detect the impact, we use the following view to monitor:

CREATE Viewdbo.cachedplans (QueryText, Queryplan, Executioncount, ObjectType, size_kb, Lastexecutiontime) asselectquerytext= Querytexts.text, queryplan= Queryplans.query_plan, executioncount= CachedPlans.usecounts, Objecttype= Cachedplans.objtype, size_kb= cachedplans.size_in_bytes/1024, Lastexecutiontime= last_execution_ Timefromsys.dm_exec_cached_plans as Cachedplanscross Applysys.dm_exec_query_plan (plan_handle) as QueryPlansCROSS Applysys.dm_exec_sql_text (plan_handle) as Querytextsinner joinsys.dm_exec_query_stats as Querystatsoncachedplans.plan_handle = Querystats.plan_handle;

This view retrieves all the schedules currently in the plan cache, including the batch documents and schedules, and the final execution time for each plan. Use the following query to check the contents of the plan cache and only query this schedule:

Select*fromdbo.cachedplanswherequerytext like n '%customers% ' andquerytext don't like n '%sys.dm_exec_cached_plans% ' ORDER Bylastexecutiontime ASC;

Therefore, the most common way to query is the following:

Selectid, Name, lastpurchasedatefrommarketing.customerswherecountry = N ' IL ';

This is the non-parameterized T-SQL query. This query cannot take advantage of parameters and will generate an independent execution plan when querying with different country codes. If you use a different country query, there will be separate plans in the cache, and the execution count is 1. As follows:

QueryText	Executioncount	ObjectType
SELECT Id, Name ... WHERE country = N ' IL ';	1	Adhoc
SELECT Id, Name ... WHERE country = N ' FR ';	1	Adhoc

The Adhoc object type indicates that it is a non-parameterized query.

The second approach is to execute the query dynamically with non-parametric, as follows:

Declare@country as NCHAR (2) = N ' IL ', @QueryText as NVARCHAR (MAX); SET @QueryText =n ' Selectid, Name, lastpurchasedatefrommarketing.customerswherecountry = N ' ' + @Country + n '; EXECUTE (@QueryText);

In this case, the dynamic query in the @querytext variable, the dynamic part is in the character stitching in. Then use the EXECUTE statement. The query is passed to the query processor in the same way as a non-parameterized query. As with non-parameterized queries, this type of query does not apply to parameters, so a separate execution plan is generated if a different country code is used.

QueryText	Executioncount	ObjectType
SELECT Id, Name ... WHERE country = N ' IL ';	1	Adhoc
SELECT Id, Name ... WHERE country = N ' FR ';	1	Adhoc

Now, we create a dynamic query, this time creating a parameter for the country, and passing parameters to the system stored procedure sys.sp_executesql.

Declare@country as NCHAR (2) = N ' IL ', @QueryText as NVARCHAR (max), @Parameters as NVARCHAR (max); SET @QueryText =n ' Selectid, Name, lastpurchasedatefrommarketing.customerswherecountry = @pCountry; '; SET @Parameters = N ' @pCountry as NCHAR (2) '; EXECUTE sys.sp_executesql@statement = @QueryText, @params = @Parameters, @pCountry = @Country;

@pCountry is a parameter within the dynamic batching range. @Parameters variable to hold all the parameters in the batch. This batch produces a parameterized plan. If you run this code in a different country code, the same preparation plan will be reused, because each execution is the same batch, with different parameters.

QueryText	Executioncount	ObjectType
(@pCountry as NCHAR (2)) SELECT Id, Name ... WHERE country = @pCountry;	2	Prepared

Note that this query document contains the parameter definitions, which define the parameters before the query.

Next, let's look at the same execution plan in the application. For example, in C #, you can build a query text and then assign the text to a value CommandText and then execute it.

As follows:

SqlConnection Connection = new SqlConnection (Properties.Settings.Default.ConnectionString); SqlCommand Command = new SqlCommand (); Command.commandtype = CommandType.Text; Command.commandtext = "Select Id, Name, lastpurchasedate from marketing.customers WHERE country = N '" + TextBox1.Text + "‘;"; Command.connection = Connection; Connection.Open (); Command.ExecuteReader (); Connection.close ();

This is essentially the same as a dynamic, non-parameterized query executed with an EXECUTE statement, and the content is the same for the execution plan cache with different country codes:

QueryText	Executioncount	ObjectType
SELECT Id, Name ... WHERE country = N ' IL ';	1	Adhoc
SELECT Id, Name ... WHERE country = N ' FR ';	1	Adhoc

But you can also embed parameters in a dynamic query, and define those parameters just like we do with Sys.sp_executesql.

SqlConnection Connection = new SqlConnection (Properties.Settings.Default.ConnectionString); SqlCommand Command = new SqlCommand (); Command.commandtype = CommandType.Text; Command.commandtext = "Select Id, Name, lastpurchasedate from marketing.customers WHERE country = @pCountryId;"; COMMAND.PARAMETERS.ADD ("@pCountryId", SqlDbType.NChar); command.parameters["@pCountryId"]. Size = 2; command.parameters["@pCountryId"]. Value = TextBox1.Text; command.parameters["@pCountryId"]. Direction = ParameterDirection.Input; Command.connection = Connection; Connection.Open (); Command.ExecuteReader (); Connection.close ();

In fact, when we run the code of this application, it is translated into exactly the same sys.sp_executesql execution plan ...

QueryText	Executioncount	ObjectType
(@pCountry as NCHAR (2)) SELECT Id, Name ... WHERE country = @pCountry;	2	Prepared

So what about the stored procedure?

CREATE Proceduremarketing.usp_customersbycountry (@Country as NCHAR (2)) Asselectid, Name, Lastpurchasedatefrommarketing.customerswherecountry = @Country; GO

As expected:

QueryText	Executioncount	ObjectType
CREATE PROCEDURE ...	2	Proc

The final approach, which looks much like parameterization, is not.

Declare@country as NCHAR (2) = N ' IL '; Selectid, Name, lastpurchasedatefrommarketing.customerswherecountry = @Country;

In this case, a local variable is declared, assigned a value, and then queried directly using the parameter. However, in fact, this is exactly equivalent to a stored procedure internal query. The most confusing thing here is that parameters and local variables start with @, but they are completely different objects.

First of all, this query is not parameterized at all, because the whole batch is compiled, contains declaration statements, and every different country, so we get different batches and schedules.

QueryText	Executioncount	ObjectType
DECLARE @Country as NCHAR (2) = N ' IL '; SELECT Id, Name ... WHERE country = @Country;	1	Adhoc
DECLARE @Country as NCHAR (2) = N ' FR '; SELECT Id, Name ... WHERE country = @Country;	1	Adhoc

The object type is Adhoc, knowing that this is a non-parametric query. is different from the plan.

Second, there is a potential performance problem with this query. In order to understand this we understand the previous method ...

When a query specifies a constant to encode this object to the country, is it hard-coded in the first method or dynamically assigned? The optimizer knows this value at compile time and uses this value to estimate the number of rows that might be returned. These estimates help the optimizer choose the best query plan. When the value is already known by the optimizer, the estimated number of rows can be counted and accurate estimates can be made in most cases.

When this query uses the country parameter, the optimizer uses a method called "parameter sniffing" (which I'll cover in detail in the next chapter). Parameter sniffing allows the optimizer to sniff the values of parameters at compile time, so when the query is optimized it is known that the parameter values the ear, just like the hard-coded parameter values. This method can only be used as a parameter and cannot be used as a local variable. Declaring and setting values to local variables occurs at run time, so the optimizer knows nothing about local variables at compile time, and the optimizer takes them as unknown parameters. The optimizer handles unknown values in different scenarios with different rules. In general, use average statistics to deal with unknown values, and sometimes this can lead to erroneous estimates.

In this article I have 7 fewer ways to execute a query and see the difference between parameterized and non-parameterized queries. In the next article I will mainly introduce parameter sniffing and parameter sniffing.

Parameterization (ii): How queries are executed