用游标更新表，速度异常慢

[eaglerxa]

AB表均为6w多条记录，用游标打开B，然后根据条件update A from B
居然10多分中还没完成。。。。。。不正常吧？？

[nzperfect]

表kuan的Temcode加索引试试

create index idx_01 on kuan(Temcode)

• 打赏
• 举报

回复 赞

[ojuju10]

可以不用游标不？

批量更新！

• 打赏
• 举报

回复 赞

[nzperfect]

这不好说,发语句吧.

• 打赏
• 举报

回复 赞

[wzy_love_sly]

case when不知道行不行，详细.

• 打赏
• 举报

回复 赞

[eaglerxa]

大概如下：
begin tran
declare cr cursor for select px, k, s , g, n, x from #tt
open cr
fetch next from cr into @Px, @k, @S, @g ,@n , @x
while @@fetch_Status = 0
begin
if @px = 1
begin
update kuan
set fs1 = @stone ,
fs1 = @Guig,
fs1 = @Num,
fs1 = @xiangf
where Temcode = @kuanh
end else
if @px =2
begin
update kuan
set fs2e = @stone ,
fs2 = @Guig,
fs2 = @Num,
fs2 = @xiangf
where Temcode = @kuanh
end else
if @px =3
.......

fetch next from cr into @Px, @kuanh, @Stone, @guig,@num , @xiangf
end
commit tran
close cr
deallocate cr

• 打赏
• 举报

回复 赞

[eaglerxa]

大概如下：
begin tran
declare cr cursor for select px, k, s , g, n, x from #tt
open cr
fetch next from cr into @Px, @k, @S, @g ,@n , @x
while @@fetch_Status = 0
begin
if @px = 1
begin
update kuan
set fs1 = @stone ,
fs1 = @Guig,
fs1 = @Num,
fs1 = @xiangf
where Temcode = @kuanh
end else
if @px =2
begin
update kuan
set fs2e = @stone ,
fs2 = @Guig,
fs2 = @Num,
fs2 = @xiangf
where Temcode = @kuanh
end else
if @px =3
.......

fetch next from cr into @Px, @k, @S, @g ,@n , @x
end
commit tran
close cr
deallocate cr

• 打赏
• 举报

回复 赞

[eaglerxa]

怎么这么乱

begin tran
declare cr cursor for select px, k,  s, g, n, x from #tt
open cr
fetch next from cr into @Px, @k, @S, @g ,@n , @x 
while @@fetch_Status = 0
begin
  	if @px = 1 
	begin
		update 	kuan
     	   	set 	fs1 = @stone ,
	       		fs1 = @Guig,
	 		fs1 = @Num,
		 	fs1 = @xiangf
		 where  Temcode = @kuanh
	end else
	if @px =2 
	begin
		update 	kuan
     	   	set 	fs2 = @stone ,
	       		fs2 = @Guig,
	 		fs2 = @Num,
		 	fs2 = @xiangf
		 where  Temcode = @kuanh
	end else
	if @px =3 
        。。。。。。。。。。。。。

  fetch next from cr into @Px, @k, @S, @g ,@n , @x 
end
commit tran
close cr
deallocate cr

• 打赏
• 举报

回复 赞

[liusaup]

此种场合使用游标当然效率低！能不使用游标就不要使用！

• 打赏
• 举报

回复 赞

[灰太狼]

游标的效率太差了，能不用最好别用

• 打赏
• 举报

回复 赞

[ChinaITOldMan]

可以用批量更新，为什么要用cursor啊

• 打赏
• 举报

回复 赞

[eaglerxa]

是啊，游标不知怎么回事
结果是用了很多次的update，一次次手动更新反倒更快，寒一个

• 打赏
• 举报

回复 赞