SQL Server-聚焦sp_executesql执行动态SQL查询性能真的比exec好?-阿里云开发者社区

开发者社区> 嗯哼9925> 正文

SQL Server-聚焦sp_executesql执行动态SQL查询性能真的比exec好?

简介:
+关注继续查看

前言

之前我们已经讨论过动态SQL查询呢?这里为何再来探讨一番呢?因为其中还是存在一定问题,如标题所言,很多面试题也好或者有些博客也好都在说在执行动态SQL查询时sp_executesql的性能比exec好,但是事实真是如此?下面我们来一探究竟。

探讨sp_executesql和exec执行动态SQL查询性能

 首先我们创建如下测试表。

复制代码
CREATE TABLE dbo.TestDynamicSQL
    (
      Col1 INT PRIMARY KEY ,
      Col2 SMALLINT NOT NULL ,
      CreatedTime DATETIME DEFAULT GETDATE() ,
      OtherValue CHAR(10) DEFAULT 'Jeffcky'
    )
GO
复制代码

接着再来插入数据,如下:

复制代码
INSERT  dbo.TestDynamicSQL
        ( Col1,
          Col2
        )
        SELECT  number + 1 ,
                number
        FROM    master..spt_values
        WHERE   type = 'P'
        ORDER BY number
复制代码

最终查询为如下测试数据:

接下来我们执行如下两个SQL查询语句,执行4次。

复制代码
SELECT  *
FROM    dbo.TestDynamicSQL
WHERE   Col2 = 3
        AND Col1 = 4
GO
 
SELECT  *
FROM    dbo.TestDynamicSQL
WHERE   Col2 = 4
        AND Col1 = 5
GO
复制代码

紧接着我们通过如下SQL语句来查询缓存计划。

复制代码
SELECT  q.text ,
        cp.usecounts ,
        cp.objtype ,
        p.* ,
        q.* ,
        cp.plan_handle
FROM    sys.dm_exec_cached_plans cp
        CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p
        CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS q
WHERE   cp.cacheobjtype = 'Compiled Plan'
        AND q.text LIKE '%dbo.TestDynamicSQL%'
        AND q.text NOT LIKE '%sys.dm_exec_cached_plans %'
复制代码

由上图可知,我们看到存在两个查询计划且每个执行了4次,也就是说每一次查询都会重新生成一个新的计划。清除查询计划缓存,通过如下命令:

DBCC FREEPROCCACHE

我们继续往下走,我们接下来通过EXEC来执行动态SQL查询,如下,执行查询完毕后再来看看查询计划次数:

复制代码
DECLARE @Col2 SMALLINT
DECLARE @Col1 INT
 
SELECT  @Col2 = 11 ,
        @Col1 = 12
 
DECLARE @SQL VARCHAR(1000)
SELECT  @SQL = 'select * from dbo.TestDynamicSQL
where Col2 = ' + CONVERT(VARCHAR(10), @Col2) + '
and Col1 = ' + CONVERT(VARCHAR(10), @Col1)
 
EXEC (@SQL)
GO
 
DECLARE @Col2 SMALLINT
DECLARE @Col1 INT
 
SELECT  @Col2 = 12 ,
        @Col1 = 13
 
DECLARE @SQL VARCHAR(1000)
SELECT  @SQL = 'select * from dbo.TestDynamicSQL
where Col2 = ' + CONVERT(VARCHAR(10), @Col2) + '
and Col1 = ' + CONVERT(VARCHAR(10), @Col1)
 
EXEC (@SQL)
GO
复制代码

这个就不做过多解释,我们依然要清除查询计划缓存,我们再利用sp_executesql来查询,如下:

复制代码
DECLARE @Col2 SMALLINT
DECLARE @Col1 INT
 
SELECT  @Col2 = 23 ,
        @Col1 = 24
 
DECLARE @SQL NVARCHAR(1000)
SELECT  @SQL = 'select * from dbo.TestDynamicSQL
where Col2 = ' + CONVERT(VARCHAR(10), @Col2) + '
and Col1 = ' + CONVERT(VARCHAR(10), @Col1)
 
EXEC sp_executesql @SQL
Go
 
 
DECLARE @Col2 SMALLINT
DECLARE @Col1 INT
 
SELECT  @Col2 = 22 ,
        @Col1 = 23
 
DECLARE @SQL NVARCHAR(1000)
SELECT  @SQL = 'select * from dbo.TestDynamicSQL
where Col2 = ' + CONVERT(VARCHAR(10), @Col2) + '
and Col1 = ' + CONVERT(VARCHAR(10), @Col1)
 
EXEC sp_executesql @SQL
GO
复制代码

对比exec执行动态SQL查询得到的结果是一模一样,正如我所演示的,我们有两个计划,每个执行次数为4。不是说sp_executesql执行动态SQL查询会重用计划缓存么,这是因为我们没有正确使用sp_executesql所以导致SQL引擎无法重用计划。

当参数值改变为语句是唯一变化时,可以使用sp_executesql代替存储过程多次执行Transact-SQL语句。 因为Transact-SQL语句本身保持不变,只有参数值发生变化,因此SQL Server查询优化器可能会重用为第一次执行生成的执行计划。

以下是正确参数化的查询方式,我们在字符串里面有一些变量,在执行的时候,我们通过其他变量传递值给它。

复制代码
DECLARE @Col2 SMALLINT ,
    @Col1 INT
SELECT  @Col2 = 3 ,
        @Col1 = 4
 
 
DECLARE @SQL NVARCHAR(1000)
SELECT  @SQL = 'select * from dbo.TestDynamicSQL
where Col2 = @InnerCol2 and Col1 = @InnerCol1' 
 
DECLARE @ParmDefinition NVARCHAR(500)
SET @ParmDefinition = N'@InnerCol2 smallint ,@InnerCol1 int'

 
EXEC sp_executesql @SQL, @ParmDefinition, @InnerCol2 = @Col2,
    @InnerCol1 = @Col1
GO
 
 
DECLARE @Col2 SMALLINT ,
    @Col1 INT
SELECT  @Col2 = 3 ,
        @Col1 = 4
 
 
DECLARE @SQL NVARCHAR(1000)
SELECT  @SQL = 'select * from dbo.TestDynamicSQL
where Col2 = @InnerCol2 and Col1 = @InnerCol1'
 
DECLARE @ParmDefinition NVARCHAR(500)
SET @ParmDefinition = N'@InnerCol2 smallint ,@InnerCol1 int'
 
 
EXEC sp_executesql @SQL, @ParmDefinition, @InnerCol2 = @Col2,
    @InnerCol1 = @Col1

GO
复制代码

我们看到只有一个计数为8的计划,而不是像我们上述那样运行查询。 我们也可以只需要声明一次,然后我们只需要在执行之前更改参数的值,如下:

复制代码
DECLARE @Col2 SMALLINT ,
    @Col1 INT
SELECT  @Col2 = 3 ,
        @Col1 = 4
 
 
DECLARE @SQL NVARCHAR(1000)
SELECT  @SQL = 'select * from dbo.TestDynamicSQL
where Col2 = @InnerCol2 and Col1 = @InnerCol1' 
 
DECLARE @ParmDefinition NVARCHAR(500)
SET @ParmDefinition = N'@InnerCol2 smallint ,@InnerCol1 int'
 
 
 
EXEC sp_executesql @SQL, @ParmDefinition, @InnerCol2 = @Col2,
    @InnerCol1 = @Col1
 
--change param values and run the same query
SELECT  @Col2 = 2 ,
        @Col1 = 3
EXEC sp_executesql @SQL, @ParmDefinition, @InnerCol2 = @Col2,
    @InnerCol1 = @Col1
复制代码

最终查询计划缓存次数和上述正确方式一致。正确使用sp_executesql对于性能非常有利,而且使用sp_executesql还可以为我们提供一些EXEC无法实现的功能。比如如何得到一个表中的行数? 利用EXEC我们需要使用一个临时表和填充,而用sp_executesql我们只需要使用一个输出变量。

复制代码
SET STATISTICS IO ON
SET STATISTICS TIME ON
--EXEC (SQL)
DECLARE @Totalcount INT ,
    @SQL NVARCHAR(100)
 
 
CREATE TABLE #temp (Totalcount INT )
SELECT  @SQL = 'Insert into #temp Select Count(*) from dbo.TestDynamicSQL'
 
EXEC( @SQL)
 
SELECT  @Totalcount = Totalcount
FROM    #temp
 
SELECT  @Totalcount AS Totalcount
 
DROP TABLE #temp
GO
 

--sp_executesql
DECLARE @TableCount INT,
@SQL NVARCHAR(100)

SELECT @SQL = N'SELECT @InnerTableCount = COUNT(*) FROM  dbo.TestDynamicSQL'
 
EXEC SP_EXECUTESQL @SQL, N'@InnerTableCount INT OUTPUT', @TableCount OUTPUT
 
SELECT @TableCount
GO
复制代码

当然除了EXEC无法实现的功能外,最重要的一点则是SP_EXECUTESQL能够防止SQL注入问题。 

总结 

执行SQL动态查询SP_EXECUTESQL比EXEC性能更好,使得存储过程能够被重用,但是存储过程能够被重用的前提则是正确使用参数,使用参数化查询,否则SP_EXECUTESQL将不会提供任何性能益处。






本文转自Jeffcky博客园博客,原文链接:http://www.cnblogs.com/CreateMyself/p/8277713.html,如需转载请自行联系原作者

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

相关文章
SQL Server 查询性能优化 相关文章
来自: SQL Server 查询性能优化——堆表、碎片与索引(一) SQL Server 查询性能优化——堆表、碎片与索引(二) SQL Server 查询性能优化——覆盖索引(一) SQL Server 查询性能优化——覆盖索引(二) SQL Server 查询性能优化——创建索引原则...
790 0
用字符串连接SQL语句并用EXEC执行时,出现名称 '‘不是有效的标识符
原文:用字符串连接SQL语句并用EXEC执行时,出现名称 '‘不是有效的标识符  用字符串连接SQL语句并用EXEC执行时,出现名称 '这里是字符串连接的一条SQL语句‘不是有效的标识符  才发现,在写exec @sql 时,忘了在@sql加(),这样写 exec (@sql) 就不会出错了!
591 0
通过手动创建统计信息优化sql查询性能案例
本质原因在于:SQL Server 统计信息只包含复合索引的第一个列的信息,而不包含复合索引数据组合的信息   来源于工作中的一个实际问题, 这里是组合列数据不均匀导致查询无法预估数据行数,从而导致无法选择合理的执行计划导致性能低下的情况 我这里把问题简单化,主要是为了说明问题 如下一张业务表,...
770 0
.NET调用osql.exe执行sql脚本创建表和存储过程
文章出处:http://wenjl520.cnblogs.com/  或  http://www.cnblogs.com/using System;using System.Diagnostics;using System.
645 0
SET STATISTICS IO和SET STATISTICS TIME 在SQL Server查询性能优化中的作用
原文:SET STATISTICS IO和SET STATISTICS TIME 在SQL Server查询性能优化中的作用 近段时间以来,一直在探究SQL Server查询性能的问题,当然也漫无目的的查找了很多资料,也从网上的大神们的文章中学到了很多,在这里,向各位大神致敬。
925 0
Sql查询原理与Select执行顺序(详细)
原文地址:点击打开链接 一切都是为了性能,一切都是为了业务 一、查询的逻辑执行顺序 (1) FROM left_table (3) join_type JOIN right_table (2) ON join_condition (4) WHERE where_condition (5) GROUP BY group_by_list (6) WITH {cube | rollup} (7
3633 0
Python:Flask使用ThreadPoolExecutor执行异步任务
Python:Flask使用ThreadPoolExecutor执行异步任务
34 0
《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一第3章 查 询 转 换
本节书摘来自华章出版社《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一 书中的第3章,作者:黄玮,更多章节内容可以访问云栖社区“华章计算机”公众号查看。
880 0
T-SQL查询:语句执行顺序
原文:T-SQL查询:语句执行顺序 读书笔记:《Microsoft SQL Server 2008技术内幕:T-SQL查询》   ===============  T-SQL查询的执行顺序 ===============      =============== T-SQL查询的示意图...
710 0
+关注
4716
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载