SQLServer遍历表----慎用游标

         在编写存储过程的时候,通常都会碰到需要遍历表的情况,这个是时候最好选择临时表的方法,而不是使用游标,如果数据量很大,慎用游标.

         现在有一个名为POI的表,里面大概存储有250万的POI记录.

         使用游标遍历的存储过程代码如下:

        declare  myCursor cursor for select MESHID,POIID from POI
	open myCursor
	declare @MESHID nvarchar(50)
	declare @POIID nvarchar(50)
	fetch next from myCursor into @MESHID,@POIID
	while(@@FETCH_STATUS=0)
		begin
		    -------------------------------------------
		    --do something you like-------------------
		    -------------------------------------------
			fetch next from myCursor into @MESHID,@POIID
		end
	close myCursor
	deallocate myCursor

       执行存储过程,总计花费2分13秒

 

       使用临时表的存储过程代码如下:

        create table tmpTable(nID bigint primary key identity(1,1),MESHID nvarchar(50),POIID nvarchar(50))
	insert into tmpTable(MESHID,POIID) select MESHID,POIID from POI
	
	declare @totalCount int
	select @totalCount=COUNT(*) from tmpTable
	declare @index int
	set @index =1 
	declare @MESHID nvarchar(50)
	declare @POIID nvarchar(50)
	while(@index<=@totalCount)
		begin
			select @MESHID=MESHID,@POIID=POIID from tmpTable where nID=@index
		    -------------------------------------------
		    --do something you like-------------------
		    -------------------------------------------
			set @index=@index+1
		end
	drop table tmpTable

       执行存储过程,总计花费53秒.