是那个update语句慢是吧,这个优化,尝试建个索引:create index idx_WSS_O_OrderReceive_c on WSS_O_OrderReceive(ordernumber)这样应该能更快找到这条要更新的记录,速度应该能快,你试试
另外,如果可以的话,最好少用游标,在2000的话,把查询结果先存到临时表,在2005及以后的版本,可以使用cte, ;with t as ( select order_userprice,ordernumber,receive_type,order_createtime, order_suptime from View_OrderSettle where userid=@userid and order_settle=0 and order_deductstate=0 and receive_gatestate=1 and order_createtime<@endTime )update WSS_O_OrderReceive set order_settle=1 from t where WSS_O_OrderReceive.ordernumber= t.ordernumber --将订单设为已结算
;with t
as
(
select order_userprice,ordernumber,receive_type,order_createtime,
order_suptime from View_OrderSettle
where userid=@userid and order_settle=0 and order_deductstate=0 and
receive_gatestate=1 and order_createtime<@endTime
)update WSS_O_OrderReceive
set order_settle=1
from t
where WSS_O_OrderReceive.ordernumber= t.ordernumber --将订单设为已结算
--这个存储过程,也需要用普通的语句来替换
exec updateUserBalance @userid=@Userid,@money=@order_userprice --累加商户余额