对Sql Server 的性能测试数据解密

　　以下是本性能试验中使用的一些代码，是我在Tsuranoff先生的代码的基础上改进的结果.

　　--------create table and populate data

　　CREATE TABLE [dbo].[NUM]

　　([n] int NOT NULL, s varchar(128) NULL, PRIMARY KEY CLUSTERED([n] ASC))

　　go

　　-- populate data

　　set nocount on

　　declare @n int, @i int

　　set @n=1000000

　　set @i = 0

　　while @n>0 begin

　　if @i = 0 begin tran

　　insert into dbo.NUM

　　select @n, convert(varchar,@n + @i * 2)

　　set @n=@n-1

　　set @i = (@i + 1) % 1000

　　if @i = 0 commit

　　end

　　GO

　　---Create stored procs

　　SET ANSI_NULLS ON

　　GO

　　SET QUOTED_IDENTIFIER ON

　　GO

　　Create procedure [dbo].[T1]

　　@total int

　　as begin

　　create table #T (n int, s varchar(128))

　　set nocount on

　　insert into #T select n,s from dbo.NUM

　　where n%100>0 and n<=@total

　　declare @res varchar(128)

　　select @res=max(s) from dbo.NUM

　　where n<=@total

　　and not exists(select * from #T where #T.n=NUM.n)

　　return @res

　　end

　　go

　　----------

　　CREATE procedure [dbo].[T2]

　　@total int

　　as

　　begin

　　create table #T (n int primary key, s varchar(128))

　　set nocount on

　　insert into #T select n,s from dbo.NUM

　　where n%100>0 and n<=@total

　　declare @res varchar(128)

　　select @res=max(s) from dbo.NUM

　　where n<=@total and

　　not exists(select * from #T where #T.n=NUM.n)

　　option(merge join) -- Query hint

　　return @res

　　end

　　go

　　-------

　　CREATE procedure [dbo].[T3]

　　@total int

　　as begin

　　create table #T (n int, s varchar(128))

　　set nocount on

　　insert into #T select n,s from dbo.Num

　　where n%100>0 and n<=@total

　　create clustered index Tind on #T (n)

　　declare @res varchar(128)

　　select @res=max(s) from dbo.Num

　　where n<=@total and

　　not exists(select * from #T

　　where #T.n=NUM.n)

　　return @res

　　end

　　go

　　--------

　　CREATE procedure [dbo].[V1]

　　@total int

　　as begin

　　declare @V table (n int, s varchar(128))

　　set nocount on

　　insert into @V select n,s from dbo.NUM

　　where n%100>0 and n<=@total

　　declare @res varchar(128)

　　select @res=max(s) from dbo.NUM

　　where n<=@total and

　　not exists(select * from @V V

　　where V.n=NUM.n)

　　return @res

　　end

　　go

　　---------------

　　CREATE procedure [dbo].[V2]

　　@total int

　　as begin

　　declare @V table (n int primary key, s varchar(128))

　　set nocount on

　　insert into @V select n,s from dbo.NUM

　　where n%100>0 and n<=@total

　　declare @res varchar(128)

　　select @res=max(s) from dbo.NUM

　　where n<=@total and

　　not exists(select * from @V V where V.n=NUM.n)

　　option(merge join) -- query hint

　　return @res

　　end

　　go

　　--------Test Code

　　declare @t1 datetime, @n int, @i int, @total int

　　set @total = 50000 -- should less than 1,000,000

　　set @n=10 --重复次数，先取小值，逐渐加大，以免用时过长

　　print @total

　　-- test T1

　　set @t1=getdate()

　　set @i = 0

　　while @i < @n begin

　　exec dbo.T1 @total

　　set @i=@i + 1

　　end

　　select datediff(ms,@t1,getdate()) * 1.0 / @n

　　-- test T2

　　set @t1=getdate()

　　set @i = 0

　　while @i < @n begin

　　exec dbo.T2 @total

　　set @i=@i + 1

　　end

　　select datediff(ms,@t1,getdate()) * 1.0 / @n

　　-- test T3

　　set @t1=getdate()

　　set @i = 0

　　while @i < @n begin

　　exec dbo.T3 @total

　　set @i=@i + 1

　　end

　　select datediff(ms,@t1,getdate()) * 1.0 / @n

　　-- test V1

　　set @t1=getdate()

　　set @i = 0

　　while @i < @n begin

　　exec dbo.V1 @total

　　set @i=@i + 1

　　end

　　select datediff(ms,@t1,getdate()) * 1.0 / @n

　　-- test V2

　　set @t1=getdate()

　　set @i = 0

　　while @i < @n begin

　　exec dbo.V2 @total

　　set @i=@i + 1

　　end

　　select datediff(ms,@t1,getdate()) * 1.0 / @n

　　-------------

　　注意，在T2和V2的代码中使用了option(merge join),这时再来看它们的查询计划图，就发现，T2和V2现在使用完全相同的查询计划.

　　带有query hint的T2的查询计划：

　　带有query hint的V2的查询计划：

　　现在，T2和V2的查询计划相同，再进行性能比较试验，就能得出完全合理的实验数据.

　　Note:单位为ms,只有T2和V2的数据是可比的,因为它们的execution plan是相同的.

文章来源于领测软件测试网 https://www.ltesting.net/