测试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更能提高数据库性能。
阅读更多

sp_executesql AND exec

01-07

CREATE PROCEDURE spPaged rn( rn @SelectStatement nvarchar(4000), rn @FromStatement nvarchar(2000), rn @WhereStatement nvarchar(4000), rn @OrderByExpression nvarchar(500), rn @AscOrDesc nvarchar(10), rn @RecordCount int, rn @PageSize int, rn @PageIndex int, rn @DoCount bit rn) rn rnAS rn rnSET NOCOUNT ON rn rnIF(@DoCount=1) rn--if do count, return the count simply rn rn EXEC('SELECT count(*) FROM '+@FromStatement+' WHERE 1=1 '+@WhereStatement) rnELSE rnBEGIN rn rndeclare @nCount as intrndeclare @nTotalPage As intrndeclare @sSelectCopy As nvarchar(2000)rndeclare @TempTable As nvarchar(100)rnrnif isnull(@WhereStatement,'') = '' rnbeginrnset @WhereStatement = '1=1'rnendrnelsernbeginrnset @WhereStatement = right(ltrim(@WhereStatement),len(ltrim(@WhereStatement))-3)rnendrnrnrnif isnull(@OrderByExpression,'') = ''rnbeginrnset @OrderByExpression = '1'rnendrnrnrnrnset @SelectStatement = 'select top 100000000 ' + @SelectStatementrn--新改的rndeclare @sql nvarchar(4000)rnset @sql=N'select a.* , identity(int,1,1) as NumberIndex into #tempTablePage1 from (' + @SelectStatement + ' from ' + @FromStatement + ' where ' + @WhereStatement + ' order by ' + @OrderByExpression + ' ' + @AscOrDesc +') as a'rnrn +N' set @nCount = (select max(NumberIndex) from #tempTablePage1)'rn +N' if @nCount % @PageSize > 0 'rn +N' set @nTotalPage = @nCount / @PageSize + 1'rn +N' else'rn +N' set @nTotalPage = @nCount / @PageSize'rnrn +N' if @PageIndex <= 0 'rn +N' set @PageIndex = 1'rn +N' else if @PageIndex > @nTotalPage'rn +N' set @PageIndex = @nTotalPage'rn +N' if @PageIndex < @nTotalPage'rn +N' select * from #tempTablePage1 where NumberIndex between (@PageIndex - 1)*@PageSize + 1 and @PageIndex*@PageSize'rn +N' else if @PageIndex = @nTotalPage'rn +N' select * from #tempTablePage1 where NumberIndex between (@PageIndex-1)*@PageSize + 1 and @nCount 'rnrnEXEC SP_EXECUTESQL @sql,N'@nCount int rn ,@PageSize int rn ,@nTotalPage int rn ,@PageIndex int ',rn @nCount rn ,@PageSizern ,@nTotalPage rn ,@PageIndex rnendrnGOrn----------------------rn发现@sql 大于nvarchar(4000)rn执行有错.但是SP_EXECUTESQL 不支持EXEC SP_EXECUTESQL @sql1+@sql2rn所以只能rnexec(@s1+@s2) 而利用exec这些参数我怎么传进字符串去而不报错误呢?如下的参数rn@nCount rn ,@PageSizern ,@nTotalPage rn ,@PageIndex

再问 Exec sp_executesql 的问题?

07-27

请高手帮忙看下,下面这段代码怎么老是报如下错误:rnrn[code=SQL]服务器: 消息 214,级别 16,状态 2,过程 sp_executesql,行 28rn过程需要参数 '@statement' 为 'ntext/nchar/nvarchar' 类型。[/code]rn[code=SQL]rnrnDECLARE @Flow_Code INTrnrn--=================================rnSET @Flow_Code=7rn---================================rnrnDECLARE @TableName VARCHAR(100)rnDECLARE @User_Code INTrnDECLARE @User_Name VARCHAR(100)rnDECLARE @COUNT INTrnDECLARE @SQL VARCHAR(1000)rnset @TableName=''rnselect @TableName=Table_Name from M_Flow_Info where Flow_Code=@Flow_CodernSET @TableName='u_'+@TableNamernrn--CREATE TABLE #list([Name] VARCHAR(100),MyCount INT,Other INT) --定义临时表rnrn--定义游标rnDECLARE change_contract_M_Flow_Info CURSOR FOR SELECT User_code,[User_Name] from M_User_Info WHERE User_IsOut=0 rn--打开游标rnOPEN change_contract_M_Flow_InfornFETCH NEXT FROM change_contract_M_Flow_Info INTO @User_Code,@User_NamernWHILE @@fetch_status = 0rnBEGINrn set @SQL=N'select @COUNT=count(c.instance_id) from (select a.instance_id from '''+@TableName+''' a inner join rn (SELECT instance_id FROM T_Node_Info WHERE Flow_Code = '+ltrim(@Flow_Code)+' AND User_Code = '+ltrim(@User_Code)+' AND Accept_Date <> '' AND Exit_Date IS NULL and Status_Code<>3) b rn on a.instance_id=b.instance_id) c' rn Exec sp_executesql @SQL,N'@COUNT int output',@COUNT outputrn INSERT INTO #list([Name],MyCount,Other)VALUES(@User_Name,@COUNT,0)rn FETCH NEXT FROM change_contract_M_Flow_Info INTO @User_Code,@User_NamernENDrnrnCLOSE change_contract_M_Flow_Info rnDEALLOCATE change_contract_M_Flow_InfornrnSELECT * FROM #listrnrn[/code]

关于exec sp_executesql 的问题?

05-15

第一段是一个存储过程分页的代码rn[code=SQL]rnCREATE proc getdatasetrn@TableList Varchar(200)='*',--搜索表的字段,比如:id,datatime,job,用逗号隔开rn@TableName Varchar(30), --搜索的表名rn@SelectWhere Varchar(500)='',--搜索条件,这里不用写where,比如:job='teacher' and class='2'rn@SelectOrderId varchar(20),--表主键字段名。比如:idrn@SelectOrder Varchar(200)='', --排序,可以使用多字段排序但主键字段必需在最前面.也可以不写,比如:order by class ascrn@intPageNo int=1, --页号rn@intPageSize int=10 ,--每页显示数rn@RecordCount int OUTPUT --总记录数(存储过程输出参数)rnas rn rndeclare @TmpSelect NVarchar(600) rndeclare @Tmp NVarchar(600) rnrnset nocount on--关闭计数rnrnset @TmpSelect = 'select @RecordCount = count(*) from '+@TableName+' where '+@SelectWherernrnexecute sp_executesql rn@TmpSelect, --执行上面的sql语句rnN'@RecordCount int OUTPUT' , --执行输出数据的sql语句,output出总记录数rn@RecordCount OUTPUTrn if (@RecordCount = 0) --如果没有贴子,则返回零rn return 0rn rn /*判断页数是否正确*/rn if (@intPageNo - 1) * @intPageSize > @RecordCount --页号大于总页数,返回错误rn return (-1)rnset nocount off--打开计数rnif @SelectWhere != '' rnbeginrnset @TmpSelect = 'select top '+str(@intPageSize)+' '+@TableList+' from '+@TableName+' where '+@SelectOrderId+' not in(select top '+str((@intPageNo-1)*@intPageSize)+' '+@SelectOrderId+' from '+@TableName+' where '+@SelectWhere +' '+@SelectOrder+') and '+@SelectWhere +' '+@SelectOrderrnprint @TmpSelectrnendrnelsernbeginrnset @TmpSelect = 'select top '+str(@intPageSize)+' '+@TableList+' from '+@TableName+' where '+@SelectOrderId+' not in(select top '+str((@intPageNo-1)*@intPageSize)+' '+@SelectOrderId+' from '+@TableName+' '+@SelectOrder+') '+@SelectOrderrnendrnexecute sp_executesql @TmpSelectrnreturn(@@rowcount)--@@ROWCOUNT,返回受上一语句影响的行数。rnGOrn[/code]rn请教的问题是:rnexecute sp_executesql rn@TmpSelect, --执行上面的sql语句rnN'@RecordCount int OUTPUT' , --执行输出数据的sql语句,output出总记录数rn@RecordCount OUTPUTrn这段代码中的N'@RecordCount int OUTPUT'是参数列表,@RecordCount OUTPUT是给参数赋值。这样理解对吗?rnrn第2段代码rn[code=SQL]rnUSE Factory;rnGOrnDECLARE @SQLString nvarchar(500);rnDECLARE @ParmDefinition nvarchar(500);rnDECLARE @SalesOrderNumber nvarchar(25);rnDECLARE @IntVariable int;rnSET @SQLString = N'SELECT @SalesOrderOUT = MAX(ExportDeptID)rn FROM As_Inflexionrn WHERE AcceptDeptID = @CustomerID';rnSET @ParmDefinition = N'@CustomerID int,rn @SalesOrderOUT nvarchar(25) OUTPUT';rnSET @IntVariable = 230;rnEXECUTE sp_executesqlrn @SQLString --需要执行的SQL语句rn ,@ParmDefinition --参数列表rn ,@CustomerID = @IntVariable --给参数赋值rn ,@SalesOrderOUT =@SalesOrderNumber OUTPUT;rn-- This SELECT statement returns the value of the OUTPUT parameter.rnSELECT @SalesOrderNumber;rn[/code]rn问题是,@SalesOrderOUT =@SalesOrderNumber OUTPUT;这句代码如果写成,@SalesOrderOUT OUTPUT 就会报错,rn但是上面那段分页代码中写成@RecordCount OUTPUT却是正确的,请问是怎么回事啊?

将存储过程exec方式改为exec sp_executesql

07-22

CREATE PROCEDURE [dbo].[MovieSelect]rn@TableNames VARCHAR(50), rn@PrimaryKey VARCHAR(50), rn@Fields VARCHAR(200)='', rn@PageSize INT, rn@CurrentPage INT, rn@Filter VARCHAR(100) = '', rn@Group VARCHAR(100) = '', rn@Order VARCHAR(100) = ''rnASrnBEGINrn DECLARE @SortColumn VARCHAR(100)rn DECLARE @Operator CHAR(2)rn DECLARE @SortTable VARCHAR(100)rn DECLARE @SortName VARCHAR(100)rn IF @Fields = ''rn SET @Fields = '*'rn IF @Filter = ''rn SET @Filter = 'WHERE 1=1'rn ELSErn SET @Filter = 'WHERE ' + @Filterrn IF @Group <>''rn SET @Group = 'GROUP BY ' + @Grouprnrn IF @Order <> ''rn BEGINrn DECLARE @pos1 INT, @pos2 INTrn SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')rn IF CHARINDEX(' DESC', @Order) > 0rn IF CHARINDEX(' ASC', @Order) > 0rn BEGINrn IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)rn SET @Operator = '<='rn ELSErn SET @Operator = '>='rn ENDrn ELSErn SET @Operator = '<='rn ELSErn SET @Operator = '>='rn SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')rn SET @pos1 = CHARINDEX(',', @SortColumn)rn IF @pos1 > 0rn SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1)rn SET @pos2 = CHARINDEX('.', @SortColumn)rn IF @pos2 > 0rn BEGINrn SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)rn IF @pos1 > 0 rn SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1)rn ELSErn SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)rn ENDrn ELSErn BEGINrn SET @SortTable = @TableNamesrn SET @SortName = @SortColumnrn ENDrn ENDrn ELSErn BEGINrn SET @SortColumn = @PrimaryKeyrn SET @SortTable = @TableNamesrn SET @SortName = @SortColumnrn SET @Order = @SortColumnrn SET @Operator = '>='rn ENDrnrn DECLARE @type varchar(50)rn DECLARE @prec intrn SELECT @type=t.name, @prec=c.precrn FROM sysobjects o rn JOIN syscolumns c on o.id=c.idrn JOIN systypes t on c.xusertype=t.xusertypern WHERE o.name = @SortTable AND c.name = @SortNamern IF CHARINDEX('char', @type) > 0rn SET @type = @type + '(' + CAST(@prec AS varchar) + ')'rnrn DECLARE @TopRows INTrn SET @TopRows = @PageSize * @CurrentPage + 1 rn EXEC('rn DECLARE @SortColumnBegin ' + @type + 'rn SET ROWCOUNT ' + @TopRows + 'rn SELECT @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + 'rn SET ROWCOUNT ' + @PageSize + 'rn SELECT ' + @Fields + ' FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + ' rn ') rnENDrnrn问题见标题?

没有更多推荐了,返回首页