参数化(二):执行查询的方式

参数化(二):执行查询的方式

 

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

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

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

 

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

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

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;

 

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

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

1

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

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

 

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

QueryText ExecutionCount ObjectType
SELECT Id , Name… WHERE Country = N’IL’; 1 Adhoc
SELECT Id , Name… WHERE Country = N’FR’; 1 Adhoc

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

2

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

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

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

QueryText ExecutionCount ObjectType
SELECT Id , Name… WHERE Country = N’IL’; 1 Adhoc
SELECT Id , Name… WHERE Country = N’FR’; 1 Adhoc

 

3

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

QueryText ExecutionCount ObjectType
(@pCountry AS NCHAR(2)) SELECT Id , Name… WHERE Country = @pCountry; 2 Prepared

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

4

接下来,让我们看一下在应用程序中相同的执行计划。例如在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 语句执行的动态非参数化查询是一样的,并且用不同的国家编码执行计划缓存内容是一样的:

QueryText ExecutionCount ObjectType
SELECT Id , Name… WHERE Country = N’IL’; 1 Adhoc
SELECT Id , Name… WHERE Country = N’FR’; 1 Adhoc

 

5

但是也可以在动态查询中嵌入参数,并且定义这些参数,就想我们用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 的执行计划…

 

QueryText ExecutionCount ObjectType
(@pCountry AS NCHAR(2)) SELECT Id , Name… WHERE Country = @pCountry; 2 Prepared

 

6

那么存储过程中又如何?

CREATE PROCEDURE
	Marketing.usp_CustomersByCountry
(
	@Country AS NCHAR(2)
)
AS

SELECT
	Id ,
	Name ,
	LastPurchaseDate
FROM
	Marketing.Customers
WHERE
	Country = @Country;
GO
正如预期:
QueryText ExecutionCount ObjectType
CREATE PROCEDURE… 2 Proc

 

7

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

DECLARE
	@Country AS NCHAR(2) = N'IL';

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

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

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

 

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

 

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

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

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

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

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

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/119728.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • 关于使用XLSTransformer.transformXLS导出Excel表格中遇到的问题

    关于使用XLSTransformer.transformXLS导出Excel表格中遇到的问题1.需求:最近拿到的一个任务,是将订单列表导出,按照订单列表导出(包括筛选条件)。背景:由于原本的订单列表查询代码太过繁重,里面夹杂的逻辑较多,再有一个是自己想尽快的熟悉公司的业务。于是决定,自己按照原本的样子重新写一个查询的逻辑。历程:原本以为这个功能会比较简单,用不了几天。原本打算用4天把这个需求搞定。结果,到今天结束用了10天。这大大的超出了我的预估。这也有开发系统故障的原因,导…

  • 计算机位移指令的作用,循环移位指令有什么作用?

    计算机位移指令的作用,循环移位指令有什么作用?循环移位指令将字节、字或双字中的各位向右或向左循环移动N位后,再送给指令的输出单元。循环移位是环形的,即被移出来的位将返回到另一端空出来的位(见图4-18)。带进位的右、左循环移位指令各位的数据与进位位一起(16位指令时一共17位)向右(或向左)循环移动n位。循环移位指令(1)16位节日彩灯的循环移位控制设计循环移位的16位彩灯控制程序,移位的时间间隔为1s,首次扫描时用M8002来设置彩灯的初…

  • Android Studio gardle 配置 ndk 指定 ABI: abiFilters 详解

    Android Studio gardle 配置 ndk 指定 ABI: abiFilters 详解一、ABI是什么ABI是ApplicationBinaryInterface的缩写。不同Android手机使用不同的CPU,因此支持不同的指令集。CPU与指令集的每种组合都有其自己的应用二进制界面(或ABI)。ABI可以非常精确地定义应用的机器代码在运行时如何与系统交互。您必须为应用要使用的每个CPU架构指定ABI。典型的ABI包含以下信息:机…

  • pcep协议什么意思_PCEP – magnate3 – 博客园[通俗易懂]

    pcep协议什么意思_PCEP – magnate3 – 博客园[通俗易懂]PCEP是什么PCEP的全称是PathComputationElementCommunicationProtocol,直译过来就是路径计算单元通信协议。简单概括就是一个通信协议,基于TCP的应用层协议,具体用来干什么,会在下面逐步介绍。PCEP的发展曲线PCEP最初是想将路由器上的CSPF功能抽取出来,实现一个集中算路的能力,最初的时候是不温不火的;后面随着SDN大热,PCEP由于具备下发…

  • lnmp动静分离[通俗易懂]

    lnmp动静分离[通俗易懂]试验环境:ip服务概括192.168.1.61mysql,nginx,php,博客分离mysql,php到其他服务器192.168.1.62mysql代替1.61的mysql192.168.1.63php代替1.61的php思路:将1.61中的mysql,php,分离到其他服务器,分离后能够正常访问到1.61上的博客即可。一.分离MySQL数据库到1.621.在1.62服务器安装mysql[root@mysqld~]#rpm-ivhhtt

  • 线性代数行列式方程求解(正交矩阵的行列式)

    线性代数行列式求值算的可真是让人CPU疼,但计算机是不累的,所以用一个c++程序帮助你验证求解行列式的值吧。本文介绍了三种算法,虽然还没有完成–.–

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号