246.比较两个SQL的执行时间

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PROC_SQL_COMP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PROC_SQL_COMP]
GO

/*--测试两组SQL的平均时间
	
	利用osql.exe来测试两组 SQL 语句的执行时间
	测试的存储过程中,调用了事务处理,所以如果测试的SQL语句有数据修改行为
	会在调用结束后自动回滚事务,确保测试不会修改数据

	已知的问题:
	1.由于 osql 调用不允许使用多行,所以存储过程中会把回车换行替换成空格
	  对于 SQL 字符串中的回车换行,一样会被替换,所以对于字符串中包含回
	  车换行的 SQL 语句,可能会产生问题,除非回车换行不对 SQL 语句产生影响
	2.由于是调用 xp_cmdshell 来执行 osql,所以要求有系统管理员的权限(比如sa)
	3.调用 osql 使用了windows身份验证,这样省去了指定用户名及密码的麻烦
	  但如果你的 sql server 禁用了windows身份验证,则需要修改存储过程的 osql 调用

*/

/*--调用示例

	exec dbo.PROC_SQL_COMP 
		'select top 1 * from sysobjects a,sysobjects b',
		'select re=''1''
		union all select 2'
--*/
create proc dbo.PROC_SQL_COMP 
@sql1 varchar(7000),	--测试的第一个sql语句
@sql2 varchar(7000),	--测试的第二个sql语句
@t int=3	--测试次数
as
set nocount on
declare @s1 varchar(8000),@s2 varchar(8000),@tt varchar(20),@head varchar(8000)
declare @tb sysname
set @tb='tempdb.dbo.[temp_'+cast(newid() as varchar(36))+']'
exec('create table '+@tb+'(id int identity(0,1),m int)')

select @s1=replace(replace(@sql1,'''',''''''''''),char(13)+char(10),' ')
	,@s2=replace(replace(@sql2,'''',''''''''''),char(13)+char(10),' ')
	,@tt=cast(@t as varchar)
	,@head='exec master..xp_cmdshell ''osql /E /d"'+db_name()+'"'
		--使用了windows身份验证,如果不支持,则改 /E 为 /U"sa" /P"密码"
exec(@head+' /Q"set xact_abort on;declare @i int,@a datetime;set @i=0;while @i<'
	+@tt+' begin select @i=@i+1,@a=getdate();begin tran;exec('''''+@s1
	+''''');rollback tran;insert '+@tb+' select datediff(ms,@a,getdate());set @a=getdate();begin tran;exec('''''
	+@s2+''''');rollback tran;insert '+@tb+' select datediff(ms,@a,getdate()) end"'',no_output')
exec('select 组号=''SQL''+cast(id%2+1 as varchar),[平均时间(毫秒)]=avg(m),[总时间(毫秒)]=sum(m),测试次数='+@tt+'
from '+@tb+' group by ''SQL''+cast(id%2+1 as varchar)
drop table '+@tb)
go