1 目的
由于大数据量时游标存在严重的性能问题,希望通过本次试验确定游标的替代
。
目前供选择的方式为:
1、 采用临时
1方式(带自增列)
declare @Count int
set @Count = 10000
declare @startTime datetime
set @startTime = getdate()
declare @i int,@RowCount int
Create table #TempTable(
iid int IDENTITY(1,1),
id int
)
insert into #TempTable(id)
select id from Test1 with (nolock) where id<=@count
set @RowCount=@@Rowcount
set @i=1
create clustered index idx_tmp On #TempTable(id) with FILLFACTOR = 100
declare @TempID int
while @i <= @RowCount
Begin
select @TempID=id from #TempTable where iid=@i
set @i=@i+1
End
drop table #TempTable
select datediff(ms,@startTime,getdate()) as '耗时','毫秒' as '单位'
2、 采用临时表2方式(不带自增列)
declare @Count int
set @Count = 10000
declare @startTime datetime
set @startTime = getdate()
declare @i int,@RowCount int
Create table #TempTable(
id int
)
insert into #TempTable(id)
select id from Test1 with (nolock) where id<=@count
set @RowCount=@@Rowcount
set @i=1
create clustered index idx_tmp On #TempTable(id) with FILLFACTOR = 100
declare @iRwCnt int
declare @TempID int
while @iRwCnt>0
Begin
select top 1 @TempID = id from #TempTable
set @iRwCnt = @@ROWCOUNT
delete from #TempTable where id = @TempID
End
drop table #TempTable
select datediff(ms,@startTime,getdate()) as '耗时','毫秒' as '单位'
3、 采用min方式
declare @Count int
set @Count = 100000
declare @startTime datetime
set @startTime = getdate()
declare @tempID int
select @tempID = min(id) from test1
while @tempID is not null
begin
select @tempID = min(id) from test1 where id > @tempID and id <=@count
end
select datediff(ms,@startTime,getdate()) as '耗时','毫秒' as '单位'
2 测试数据
2.1 测试准备
创建数据库,并创建表 test1
create table test1(
id int IDENTITY(1,1),
name nvarchar(12) not null,
age int not null
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
id ASC
) ON [PRIMARY]
)
GO
向test1加入100万条数据
declare @i int
set @i=1
while @i<=1000000
Begin
insert into Test1(name,age) values ('tunynet',@i)
set @i=@i+1
End
2.2 测试基准(使用游标)
declare @Count int
set @Count=100000
declare @startTime datetime
set @startTime = getdate()
declare @id int
declare @tempid int
DECLARE ID_Cursor CURSOR FOR SELECT id FROM Test1 with (nolock) where id <=@Count
OPEN ID_Cursor
FETCH NEXT FROM ID_Cursor INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
set @tempid=@id
FETCH NEXT FROM ID_Cursor INTO @id
END
CLOSE ID_Cursor
DEALLOCATE ID_Cursor
select datediff(ms,@startTime,getdate()) as '耗时','毫秒' as '单位'
2.3 数据
方式
1万条数据
10万数据
100万数据
游标
4020
4006
4123
42270
42836
42750
临时表1
8610
9043
8486
临时表2
33
43
40
296
316
306
4023
3376
3016
min
180
203
196
2000
1783
1750
根据以上测试数据,应该采用“临时表2”的方式来替换游标。
附录:
参考资料: