参数化（二）：执行查询的方式

前面一篇我介绍了执行计划缓存以及执行之前批处理经过的流程。这篇将用几个最普通的例子介绍查询的几种执行方式。

请看下面这个我使用的这个查询:

SELECT	Id ,	Name ,	LastPurchaseDateFROM	Marketing.CustomersWHERE	Country = N'IL';

     这是一个简单的检索指定国家的顾客的查询。现在我们来测试前面这个查询，并且展示七个不同的查询方式。同时介绍执行方法对计划缓存和计划重用的影响。

     为了检测影响，我们使用下面的视图监视:

CREATE VIEW	dbo.CachedPlans(	QueryText ,	QueryPlan ,	ExecutionCount ,	ObjectType ,	Size_KB ,	LastExecutionTime)ASSELECT	QueryText			= QueryTexts.text ,	QueryPlan			= QueryPlans.query_plan ,	ExecutionCount		= CachedPlans.usecounts ,	ObjectType			= CachedPlans.objtype ,	Size_KB			= CachedPlans.size_in_bytes / 1024 ,	LastExecutionTime	= last_execution_timeFROM	sys.dm_exec_cached_plans AS CachedPlansCROSS APPLY	sys.dm_exec_query_plan (plan_handle) AS QueryPlansCROSS APPLY	sys.dm_exec_sql_text (plan_handle) AS QueryTextsINNER JOIN	sys.dm_exec_query_stats AS QueryStatsON	CachedPlans.plan_handle = QueryStats.plan_handle;

     这个视图检索所有的当前在计划缓存中的计划，包含批处理的文档和计划以及每个计划的最终执行时间。使用下面这个查询来检查计划缓存的内容，只查询本次计划:

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

因此， 最为普通的方式查询就是下面这种:

SELECT	Id ,	Name ,	LastPurchaseDateFROM	Marketing.CustomersWHERE	Country = N'IL';

    这就是非参数化T-SQL查询。这个查询不能利用参数，用不同的国家编码查询时会产生独立的执行计划。如果使用不同的国家查询，就会有独立计划在缓存中，并且执行的计数为1。如下：

Adhoc对象类型表示它是一个非参数化查询。

第二种方式是用非参数化动态执行查询，具体如下:

DECLARE@Country      AS NCHAR(2)                = N'IL' ,@QueryText    AS NVARCHAR(MAX);SET @QueryText =N'SELECTId ,Name ,LastPurchaseDateFROMMarketing.CustomersWHERECountry = N''' + @Country + N''';';EXECUTE (@QueryText);

在这种情况下，在@QueryText变量中的动态查询，动态部分是在用字符拼接进去。然后使用EXECUTE 语句。查询被传递给查询处理器这点与非参数化查询一样。与非参数化查询一样，这种查询也不适用参数，因此如果用不同的国家编码，还是产生独立的执行计划。

   现在，我们建立一个动态查询，这次为国家创建一个参数，并且传递参数给系统存储过程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 是动态批处理范围内的参数。@Parameters 变量保存所有的批处理中的参数。这个批处理产生一个参数化计划。如果用不同的国家编码运行这个代码，会重用相同的准备计划，因为每个执行就是一个相同的批处理，只有参数不同。

注意，这个查询文档包含参数定义，查询之前定义参数。

接下来，让我们看一下在应用程序中相同的执行计划。例如在C#中，可以建一个查询文本，然后把这个文本赋值CommandText然后执行。

如下：

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

本质上这是与一个用EXECUTE 语句执行的动态非参数化查询是一样的，并且用不同的国家编码执行计划缓存内容是一样的:

但是也可以在动态查询中嵌入参数，并且定义这些参数，就想我们用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();

实际上，当我们运行这个应用程序的代码时，它被转译成完全相同的sys.sp_executesql 的执行计划…

那么存储过程中又如何？

CREATE PROCEDURE	Marketing.usp_CustomersByCountry(	@Country AS NCHAR(2))ASSELECT	Id ,	Name ,	LastPurchaseDateFROM	Marketing.CustomersWHERE	Country = @Country;GO

正如预期：

最后一种方式，看起来很像参数化，其实不然。

DECLARE	@Country AS NCHAR(2) = N'IL';SELECT	Id ,	Name ,	LastPurchaseDateFROM	Marketing.CustomersWHERE	Country = @Country;

这个情况下，声明了一个局部变量，并赋值，然后使用参数直接查询。但是，事实上，这是完全等同于存储过程内部查询的。这里最容易混淆的事情就是参数和局部变量都是以@开头的，然而它们是完全不同的对象。

首先，这个查询完全不是参数化，因为整个批处理被编译，包含声明语句，以及每一个不同的国家，所以我们得到不同的批处理和计划。

对象类型是Adhoc，得知这就是个非参数化查询。是不同的计划。

其次，这个查询有潜在的性能问题。为了理解这个我们理解一下之前的方法…

当查询指定一个常量给国家编码这个对象时，它是否是硬编码在第一个方法中还是动态赋值？优化器在编译时知道这个值并且使用这个值去估算可能返回的行数。这几个估算帮助优化器选择最佳的查询计划。当这个值已经被优化器知道时，就能统计这个估算行数，并且绝大多数情况下能提出精准的估计。

当这个查询使用国家这个参数时，优化器使用一个方法叫做“参数嗅探”（下一章我会详细介绍）。参数嗅探能让优化器在编译时嗅探参数的值，因此当优化查询时是知道这个参数值耳朵，就像被硬编码参数值一样。这个方法只能用作参数不能用作局部变量。声明和设定值给局部变量都发生在运行时，因此在编译时优化器对局部变量一无所知，同时优化器把他们当做未知参数。优化器用不同的规则处理不同场景下的未知值。一般来说，使用平均统计应对未知值，有些时候这样做就会导致错误的估计。

本篇我就少了7种方式来执行查询，并且看到参数化与非参数化查询的区别。下一篇我将主要介绍参数嗅探以及参数嗅探的好坏。

海外公司注册、海外银行开户、跨境平台代入驻、VAT、EPR等知识和在线办理：https://www.xlkjsw.com

原标题：参数化（二）：执行查询的方式

关键词：