以下是本性能試驗中使用的一些代碼，是我在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的查詢計劃相同，再進行性能比較試驗，就能得出完全合理的實驗數據.
N | T1 | T2 | T3 | V1 | V2 |
| 100 | 11.9 | 8 | 11.3 | 8.4 | 6.8 |
| 1000 | 166 | 23 | 36 | 239 | 19 |
| 10000 | 366 | 304 | 384 | 17695 | 290 |
| 100000 | 3338 | 3740 | 3653 | 太長 | 3586 |
| 500000 | 21040 | 25096 | 18076 | 太長 | 20036 |
| 1000000 | 37716 | 87783 | 68246 | 太長 | 40956 |
Note:單位為ms,只有T2和V2的數據是可比的,因為它們的execution plan是相同的.
文章來源于領測軟件測試網 http://www.kjueaiud.com/
