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将不会提供任何性能益处。

巴特西

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

前言

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

总结

最新文章

热门文章