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更能提高数据库性能。