游标替代方案

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”的方式来替换游标。 附录： 参考资料：