游标问题－－－在线等待

[myhunan]

我声明一个游标后，发现游标几乎将CPU的资源耗尽了，其实它处理的数据量也不是很大，只有两万条左右的记录，都处理一两个小时了，还没处理完，请教各位大侠，有无更好的办法解决此种情况的处理速度问题

[zqllyh]

把游标贴出来，看看能不能不用游标。

[myhunan]

truncate table c_customer_money
go
-------------------------------------插入数据---------------------------------
insert into c_customer_money
select zjzh,hbdm,'','','','','','','',zjye,zjkys,(zjmrdjs+zjmcdjs+zjycdjs),zqsz,(zjkys + zqsz),0,0,0,0,0,0,lastczrq from zjk
--------------------------------------修改其基本资料---------------------
go
declare @custaid char(12)
declare @orgaid char(6)
declare @class char(2)
declare @costid char(2)
declare @sex char(1)
declare @age char(2)
declare @knowtype char(2)
declare Cur_zjzh cursor for
select custaid,orgaid,class,costid,sex,age,knowtype
from c_customer
order by custaid
open Cur_zjzh
Fetch next from Cur_zjzh into @custaid,@orgaid,@class,@costid,@sex,@age,@knowtype
While (@@Fetch_status = 0)
Begin
update c_customer_money
set orgaid = @orgaid,class = @class,costid = @costid,sex = @sex,age = @age,knowtype = @knowtype
where custaid = @custaid
Fetch next from Cur_zjzh into @custaid,@orgaid,@class,@costid,@sex,@age,@knowtype
End
Close Cur_zjzh
Deallocate Cur_zjzh


GO

[CrazyFor]

给custaid字段加索引，能快很多，

[CrazyFor]

最好加聚集索引，

你的存储过程中两个耗时比较大的地方。
1，select custaid,orgaid,class,costid,sex,age,knowtype
from c_customer
order by custaid 中的order by custaid

2，update c_customer_money
set orgaid = @orgaid,class = @class,costid = @costid,sex = @sex,age = @age,knowtype = @knowtype
where custaid = @custaid中的where custaid = @custaid