测试sp_executesql和exec的性能差别

sp_executesql扩展存储过程与t-sql的execute功能相似，但有一点不同，通过sp_executesql执行的执行计划会被缓存起来，可重复使用。

测试：nz.perfectaction nzperfect@gmail.com 下面 测试sp_executesql和exec的性能差别 Create DATABASE T_DB

GO

USE T_DB

GO

Create TABLE TB

( ID INT IDENTITY(1,1) PRIMARY KEY,NAME VARCHAR(20))

GO

Insert INTO TB Select 'A'

Insert INTO TB Select 'B'

Insert INTO TB Select 'C'

Insert INTO TB Select 'D'

Insert INTO TB Select 'E'

Insert INTO TB Select 'F'

GO

-- 清除缓存中所有元素

DBCC FREEPROCCACHE

 

-- 查看 T_DB 数据库使用的缓存

Select SQL AS EXEC_SQL,OBJTYPE AS EXEC_TYPE,* FROM MASTER..SYSCACHEOBJECTS Where DBID=DB_ID('T_DB')

AND SQL LIKE '%Select * FROM TB Where NAME%' AND SQL NOT LIKE '%SYSCACHEOBJECTS%'

ORDER BY SQL

结果为空，

-- 测试使用 EXEC，执行下面sql语块

DECLARE @SQL VARCHAR(2000)

DECLARE @NAME VARCHAR(20)

DECLARE @I INT

SET @I=1

WHILE @I<=6

BEGIN

    IF @I=1 SET @NAME='A' IF @I=2 SET @NAME='B' IF @I=3 SET @NAME='C'

    IF @I=4 SET @NAME='D' IF @I=5 SET @NAME='E' IF @I=6 SET @NAME='F'

    SET @SQL = 'Select * FROM TB Where NAME = '''+@NAME+''''

    EXEC(@SQL)

    SET @I = @I + 1

END

 

-- 查看 T_DB 数据库使用的缓存

Select SQL AS EXEC_SQL,OBJTYPE AS EXEC_TYPE,* FROM MASTER..SYSCACHEOBJECTS Where DBID=DB_ID('T_DB')

AND SQL LIKE '%Select * FROM TB Where NAME%' AND SQL NOT LIKE '%SYSCACHEOBJECTS%'

ORDER BY SQL

结果有六条记录如图 :

这说明sql server 对于exec执行的sql语句，即使where字段是同一个，但值不一样，每次都需要重新编译，而使用不同的缓存。

 

-- 测试使用 SP_EXECUTESQL，执行下面sql语块

DECLARE @SQL NVARCHAR(2000)

DECLARE @NAME NVARCHAR(20)

DECLARE @I INT

SET @I=1

WHILE @I<=6

BEGIN

    IF @I=1 SET @NAME='A' IF @I=2 SET @NAME='B' IF @I=3 SET @NAME='C'

    IF @I=4 SET @NAME='D' IF @I=5 SET @NAME='E' IF @I=6 SET @NAME='F'

    SET @SQL = 'Select * FROM TB Where NAME = @NAME'

    EXEC SP_EXECUTESQL @SQL,N'@NAME NVARCHAR(20)',@NAME

    SET @I = @I + 1

END

 

-- 查看缓存

Select SQL AS EXEC_SQL,OBJTYPE AS EXEC_TYPE,* FROM MASTER..SYSCACHEOBJECTS Where DBID=DB_ID('T_DB')

AND SQL LIKE '%Select * FROM TB Where NAME%' AND SQL NOT LIKE '%SYSCACHEOBJECTS%'

ORDER BY SQL

结果除了刚才的六条记录，又增加一条记录如图 :

如上图，说明sql server 对于sp_executesql执行的sql语句，只要where字段是相同的，尽管值不同，都不再需要重新编译，而执行使用同一个缓存计划。

 

-- 测试完毕

Drop DATABASE T_DB

GO

Drop TABLE TB

GO

总结，sp_executesql执行计划会被缓存，而execute不可以，如果大量重复查询，sp_executesql比execute更能提高数据库性能。