Parameterization: how queries are executed

In the previous article, I introduced the execution plan cache and the process of batch processing before execution. This article will introduce several execution methods of queries with some of the most common examples.

Look at the following query I used:

SELECT
	Id ,
	Name ,
	LastPurchaseDate
FROM
	Marketing.Customers
WHERE
	Country = N'IL';

This is a simple query to retrieve customers in a specified country. Now let's test the previous query and show seven different query methods. It also introduces the impact of execution methods on plan caching and plan reuse.

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

CREATE VIEW
	dbo.CachedPlans
(
	QueryText ,
	QueryPlan ,
	ExecutionCount ,
	ObjectType ,
	Size_KB ,
	LastExecutionTime
)
AS

SELECT
	QueryText			= QueryTexts.text ,
	QueryPlan			= QueryPlans.query_plan ,
	ExecutionCount		= CachedPlans.usecounts ,
	ObjectType			= CachedPlans.objtype ,
	Size_KB			= CachedPlans.size_in_bytes / 1024 ,
	LastExecutionTime	= last_execution_time
FROM
	sys.dm_exec_cached_plans AS CachedPlans
CROSS APPLY
	sys.dm_exec_query_plan (plan_handle) AS QueryPlans
CROSS APPLY
	sys.dm_exec_sql_text (plan_handle) AS QueryTexts
INNER JOIN
	sys.dm_exec_query_stats AS QueryStats
ON
	CachedPlans.plan_handle = QueryStats.plan_handle;

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

SELECT
	*
FROM
	dbo.CachedPlans
WHERE
	QueryText LIKE N'%Customers%'
AND
	QueryText NOT LIKE N'%sys.dm_exec_cached_plans%'
ORDER BY
	LastExecutionTime ASC;

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

SELECT
	Id ,
	Name ,
	LastPurchaseDate
FROM
	Marketing.Customers
WHERE
	Country = N'IL';

This is a nonparametric T-SQL query. This query cannot use parameters. When querying with different country codes, independent execution plans will be generated. If different country queries are used, there are independent 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 ad hoc object type indicates that it is a nonparametric query.

The second method is to execute the query dynamically with non parameterization, as follows:

DECLARE
@Country      AS NCHAR(2)                = N'IL' ,
@QueryText    AS NVARCHAR(MAX);
SET @QueryText =
N'
SELECT
Id ,
Name ,
LastPurchaseDate
FROM
Marketing.Customers
WHERE
Country = N''' + @Country + N''';
';
EXECUTE (@QueryText);

In this case, the dynamic part of the dynamic query in the @ QueryText variable is spliced with characters. Then use the EXECUTE statement. The query is passed to the query processor, just like a nonparametric query. Like nonparametric queries, this query does not apply to parameters. Therefore, if different country codes are used, an independent execution plan will be generated.

QueryText

ExecutionCount

ObjectType

SELECT Id , Name... WHERE Country = N'IL';

1

Adhoc

SELECT Id , Name... WHERE Country = N'FR';

1

Adhoc

Now, let's create a dynamic query, this time create a parameter for the country, and pass the parameter 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'
SELECT
Id ,
Name ,
LastPurchaseDate
FROM
Marketing.Customers
WHERE
Country = @pCountry;
';
SET @Parameters = N'@pCountry AS NCHAR(2)';
EXECUTE sys.sp_executesql
@statement    = @QueryText ,
@params       = @Parameters ,
@pCountry     = @Country;

@pCountry is a parameter within the scope of dynamic batch processing@ The Parameters variable holds the Parameters in all batches. This batch produces a parameterized plan. If you run this code with different country codes, 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 the query document contains parameter definitions, which are defined before the query.

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

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();

In essence, this is the same as a dynamic nonparametric query executed with EXECUTE statement, and the cache contents of execution plans encoded in different countries are the same:

QueryText

ExecutionCount

ObjectType

SELECT Id , Name... WHERE Country = N'IL';

1

Adhoc

SELECT Id , Name... WHERE Country = N'FR';

1

Adhoc

However, you can also embed parameters in dynamic queries and define these parameters, just as we use sys.sp_executesql is the same as executing stored procedures.
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 the exact same sys.sp_executesql's execution plan

QueryText

ExecutionCount

ObjectType

(@pCountry AS NCHAR(2)) SELECT Id , Name... WHERE Country = @pCountry;

2

Prepared

What about stored procedures?
CREATE PROCEDURE
	Marketing.usp_CustomersByCountry
(
	@Country AS NCHAR(2)
)
AS

SELECT
	Id ,
	Name ,
	LastPurchaseDate
FROM
	Marketing.Customers
WHERE
	Country = @Country;
GO
As expected:

QueryText

ExecutionCount

ObjectType

CREATE PROCEDURE...

2

Proc

The last method looks like parameterization, but it's not.
DECLARE
	@Country AS NCHAR(2) = N'IL';

SELECT
	Id ,
	Name ,
	LastPurchaseDate
FROM
	Marketing.Customers
WHERE
	Country = @Country;

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

First, this query is not parameterized at all, because the whole batch is compiled, including declaration statements, and each different country, so we get different batches and plans.

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 ad hoc. We know that this is a nonparametric query. It's a different plan.

Second, this query has potential performance problems. To understand this, let's understand the previous method

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

When this query uses the country parameter, the optimizer uses a method called "parameter sniffing" (which I will describe in detail in the next chapter). Parameter sniffing allows the optimizer to sniff the value of the parameter at compile time. Therefore, when optimizing the query, you know the parameter value, just like the hard coded parameter value. This method can only be used as a parameter, not a local variable. Both declaration and setting values to local variables occur at run time, so the optimizer knows nothing about local variables at compile time, and the optimizer treats them as unknown parameters. The optimizer uses different rules to deal with unknown values in different scenarios. In general, using average statistics to deal with unknown values will sometimes lead to wrong estimates.

In this article, I have omitted seven ways to execute queries, and see the difference between parameterized and nonparametric queries. In the next article, I will mainly introduce parameter sniffing and the quality of parameter sniffing.

Publisher: full stack programmer, stack length, please indicate the source for Reprint: https://javaforall.cn/119728.html Original link: https://javaforall.cn

Posted on Mon, 29 Nov 2021 03:15:36 -0500 by saadatshah