开发者社区> jeffcky> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介: 之前我们已经讨论过动态SQL查询呢?这里为何再来探讨一番呢?因为其中还是存在一定问题,如标题所言,很多面试题也好或者有些博客也好都在说在执行动态SQL查询时sp_executesql的性能比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

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

接下来我们执行如下两个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 %'

2

由上图可知,我们看到存在两个查询计划且每个执行了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

3

这个就不做过多解释,我们依然要清除查询计划缓存,我们再利用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

4

对比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

5

我们看到只有一个计数为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

6

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

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

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

相关文章
SQL Server-聚焦sp_executesql执行动态SQL查询性能真的比exec好?
原文:SQL Server-聚焦sp_executesql执行动态SQL查询性能真的比exec好? 前言 之前我们已经讨论过动态SQL查询呢?这里为何再来探讨一番呢?因为其中还是存在一定问题,如标题所言,很多面试题也好或者有些博客也好都在说在执行动态SQL查询时sp_executesql的性能比exec好,但是事实真是如此?下面我们来一探究竟。
1069 0
SQL Server基础之<存储过程>
原文:SQL Server基础之   简单来说,存储过程就是一条或者多条sql语句的集合,可视为批处理文件,但是其作用不仅限于批处理。本篇主要介绍变量的使用,存储过程和存储函数的创建,调用,查看,修改以及删除操作。
1420 0
SQLSERVER存储过程语法详解
SQL SERVER存储过程语法: Create PROC [ EDURE ] procedure_name [ ; number ]     [ { @parameter data_type }         [ VARYING ] [ = default ] [ OUTPUT ]     ] [ ,...n ]   [ WITH     { RECOMPILE | ENCRY
1580 0
Sql Server 存储过程基础
        将常用的或很复杂的工作,预先用SQL语句写好并用一个指定的名称存储起来, 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用execute,即可自动完成命令。       存储过程的优点:        1.存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度
956 0
SQL Server 存储过程
Transact-SQL中的存储过程,非常类似于Java语言中的方法,它可以重复调用。当存储过程执行一次后,可以将语句缓存中,这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。 Ø 存储过程的概念     存储过程Procedure是一组为了完成特定功能的SQL语句集合,经编译后存储在数据库中,用户通过指定存储过程的名称并给出参数来执行。
719 0
+关注
jeffcky
耕耘博客、走进技术的世界!
174
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载