測試sp_executesql和exec的效能差別)

 

sp_executesql擴充預存程序與t-sql的execute功能相似，但有一點不同，通過sp_executesql執行的執行計畫會被緩衝起來，可重複使用。測試：nz.perfectaction nzperfect@gmail.com 下面測試sp_executesql和exec的效能差別 Create DATABASE T_DBGOUSE T_DBGOCreate TABLE TB(ID INT IDENTITY(1,1) PRIMARY KEY,NAME VARCHAR(20))GOInsert 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 INTSET @I=1WHILE @I<=6BEGIN    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 + 1END--查看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 INTSET @I=1WHILE @I<=6BEGIN    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 + 1END--查看緩衝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_DBGODrop TABLE TBGO總結，sp_executesql執行計畫會被緩衝，而execute不可以，如果大量重複查詢，sp_executesql比execute更能提高資料庫效能。