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 調用

--鄒建 2004.08(引用請保留此信息)--*/

/*--調用示例

	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