在编写存储过程的时候,通常都会碰到需要遍历表的情况,这个是时候最好选择临时表的方法,而不是使用游标,如果数据量很大,慎用游标.
现在有一个名为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秒.