都说循环效率比游标高,尤其是大数据量的时候,可为啥我做的测试结果是游标效率很高, 请高人指点.以下是我做测试的语句
SQL Server 2005
/*游标语句*/
--32万条数据用了14秒
DECLARE @EMPLOYEE VARCHAR(10)
declare @begin varchar(30)
declare @end varchar(30)
declare @cnt int
set @cnt=1
set @begin=convert(varchar(23),getdate(),121)
print @begin
DECLARE CURSOR_AUTO_ALL CURSOR FOR
select em_no from users
OPEN CURSOR_AUTO_ALL
FETCH NEXT FROM CURSOR_AUTO_ALL INTO @EMPLOYEE
WHILE @@FETCH_STATUS=0
BEGIN
set @cnt=@cnt+1
FETCH NEXT FROM CURSOR_AUTO_ALL INTO @EMPLOYEE
END
set @end=convert(varchar(23),getdate(),121)
print @end
print @cnt
CLOSE CURSOR_AUTO_ALL
DEALLOCATE CURSOR_AUTO_ALL
/*循环*/
--32万条数据用了....过了9分钟还没运行完....
declare @employee varchar(30)
declare @begin varchar(30)
declare @end varchar(30)
declare @cnt int
set @cnt=1
set @begin=convert(varchar(23),getdate(),121)
print @begin
select identity(int,1,1) as id,em_no into #us from users
set @cnt=@@rowcount
while @cnt>0
begin
select top 1 @employee=em_no from #us where id=@cnt
set @cnt=@cnt-1
end
drop table #us
set @end=convert(varchar(23),getdate(),121)
print @end
