SQL Server使用变量和参数以及语句执行时执行计划的差异-阿里云开发者社区

开发者社区> 阿里云支持与服务> 正文
登录阅读全文

SQL Server使用变量和参数以及语句执行时执行计划的差异

简介: T-SQL语句之间传输数据有以下一些途径: 1)本地变量 2)存储过程中的参数 3)应用程序变量 4)参数标记 上面4种变量中,比较常用的是本地变量和存储过程中的参数。 本地变量指在查询前declare参数,并且set设值之后,在查询语句中直接使用声明的参数,而不是直接使用其值。

T-SQL语句之间传输数据有以下一些途径:
1)本地变量
2)存储过程中的参数
3)应用程序变量
4)参数标记
上面4种变量中,比较常用的是本地变量和存储过程中的参数。

本地变量指在查询前declare参数,并且set设值之后,在查询语句中直接使用声明的参数,而不是直接使用其值。

存储过程中的参数有两种,一种是定义在存储过程里面,类似上面的本地变量,一种是定义在存储过程外面。这两种参数,都是在执行存储过程时,将参数值传入进去。不过两种定义方法各有优缺点,下一章将进行深入探讨。

应用程序变量主要指编程语言C,C++,Basic和Java等,它们都有自己的一套变量来存储值。应用程序一般通过数据库的API把T-SQL语句返回的值存储到自己的变量中,然后再使用这些值。

参数标记是放置在 T-SQL 语句中的输入表达式位置的一个问号 (?)。参数标记绑定了应用程序变量,似的应用程序变量中的数据可以用作T-SQL语句中的输入。参数标记还允许存储过程输出参数和返回代码绑定到应用程序变量。

本专题主要介绍本地变量和存储过程中的参数的定义,使用以及语句执行时执行计划的差异。

注: 下面每种情况执行之前都执行DBCC FREEPROCCACHE清除了缓存,生产环境避免执行该语句。

不使用变量和参数

直接在SSMS中赋值,SQL Server在编译时知道具体的值,给出的执行计划很准确。
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person AS p
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE 'Man%';
screenshot.png
开启SQL Server Profiler抓取SP:CacheHit和SP:CacheMiss事件,可以发现where条件换个数据后,SQL Server会根据当前的值重新生成执行计划。
screenshot.png
screenshot.png

使用本地变量

首先我们来看看本地变量的定义及使用方法。
/* Also allowed:
DECLARE @find varchar(30) = 'Man%';
*/
DECLARE @find varchar(30);
SET @find = 'Man%';
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person AS p
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @find;
使用本地变量,SQL Server在执行语句时,根据变量的预估值进行编译,给出的执行计划比较准确。
screenshot.png
抓取SQL Server Profiler,会发现使用本地变量每次执行都会重新编译,生成新的执行计划。
screenshot.png
screenshot.png

将变量定义在存储过程里面

示例:
--local variable
create procedure sniff(@find varchar(30)) as
DECLARE @findIn varchar(30);
set @findIn=@find
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person AS p
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @findIn;
go
与本地变量一样,它的值在存储过程语句执行过程中得到,SQL Server在运行时不知道变量的值,会根据一个预估值进行编译,给出一个折中的执行计划。
exec sniff 'Man%';
screenshot.png
exec sniff 'U%';
screenshot.png

将变量定义在存储过程外面

示例:
create procedure sniff2(@find varchar(30)) as
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person AS p
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @find;
go
当调用存储过程时,必须要给他带入值。第一次运行存储过程时,SQL Server根据带入的变量值进行编译,给出准确的执行计划。以后再次调用存储过程,SQL Server会重用第一次的执行计划,不会进行重编译,以达到较高的效率,这是存储过程的一大优势。
exec sniff2 'Man%';
screenshot.png
screenshot.png
exec sniff2 'U%';
screenshot.png
screenshot.png

不少客户在SQL语句中使用本地变量,会出现同一语句有时快有时慢的情况,从上面的测试应该可以得到答案了。使用存储过程,最大的好处就是可以重用执行计划,大大提高执行效率,但是这一优势同时也是存储过程的最大劣势,首次编译的执行计划如果不适用于新的参数值,有可能会让语句执行时间反很多倍,这就是著名的parameter sniffing,下一章我们将继续深入探讨。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:

分享阿里云支持与服务团队最佳实践、经典案例与故障排查。

官方博客
最新文章
相关文章
文档