现在的位置: 首页 > 综合 > 正文

测试sp_executesql和exec的性能差别

2013年12月16日 ⁄ 综合 ⁄ 共 1864字 ⁄ 字号 评论关闭
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更能提高数据库性能。

抱歉!评论已关闭.