22,207
社区成员
发帖
与我相关
我的任务
分享
DECLARE @depot nvarchar(10)
SELECT @depot='00000'
WHILE 1=1
BEGIN
SELECT @depot =depot FROM s_depot WHERE depot > @depot ORDER BY depot desc
IF @@rowcount <1
BREAK
PRINT @depot
END
DECLARE @depot nvarchar(10)
DECLARE cur1 CURSOR FOR SELECT depot FROM s_depot ORDER BY depot
OPEN cur1
FETCH next FROM cur1 INTO @depot
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @depot
FETCH next FROM cur1 INTO @depot
END
CLOSE cur1
DEALLOCATE cur1
--生成测试表,10000条数据
create table t_test(id int identity(1,1) primary key,class_a varchar(50),class_b varchar(50),add_dt datetime)
go
declare @i int
select @i = 1
while @i < = 10000
begin
insert into t_test(class_a,class_b,add_dt)
select case @i%2 when 0 then 'class_a' + cast(@i as varchar) else cast(@i as varchar) end,
case @i%5 when 0 then 'class_b' + cast(@i as varchar) else cast(@i as varchar) end, getdate()
select @i = @i + 1
end
--测试第一种所需时间
declare @t1 datetime,@t2 datetime
declare @a int,@b int
select @t1 = getdate()
declare @depot nvarchar(10)
select @depot='2'
while 1=1
begin
select @depot =id from t_test where id > @depot order by id desc
if @@rowcount <1
break
print @depot
end
select @t2 = getdate()
select cast(datediff(ms,@t1,@t2) as varchar)+'ms'
--结果:
30983ms
--测试第二种:
declare @t1 datetime,@t2 datetime
declare @a int,@b int
select @t1 = getdate()
declare @depot nvarchar(10)
declare cur1 cursor for select id from t_test where id between 3 and 10000 order by id
open cur1
fetch next from cur1 into @depot
while @@fetch_status = 0
begin
print @depot
fetch next from cur1 into @depot
end
close cur1
deallocate cur1
select @t2 = getdate()
select cast(datediff(ms,@t1,@t2) as varchar)+'ms'
--结果:
623ms