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

待我称王封你为后i 2023-12-27 06:06 17阅读 0赞

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