declare @t_t_time int, @t_pro_id uniqueidentifier, @projcode int
declare CaseFlow_cur cursor FAST_FORWARD read_only for select receive_object_id, projcode from ##CaseFlow
Open CaseFlow_cur
FETCH NEXT FROM CaseFlow_cur INTO @t_pro_id, @projcode
WHILE @@FETCH_STATUS = 0
BEGIN
select top 1 @t_t_time = isnull(t_time,0)
from szum_sase_timelimit_apply
where dept_id=@t_pro_id and projcode = @projcode
if @@rowcount = 0
begin
select @t_t_time = isnull(t_time,0) from szum_event_time
where smallclass_id = ( select smallclass_id from szum_event where fid = @projcode )
if @t_t_time = 0
set @t_t_time = -999999
end select @t_t_time = isnull(@t_t_time,0) + isnull(kc_time,0) from szum_event_time
where smallclass_id =( select smallclass_id from szum_event where fid = @projcode )update ##CaseFlow set LimitTime=@t_t_time where projcode = @projcode FETCH NEXT FROM CaseFlow_cur INTO @t_pro_id, @projcode
END
Close CaseFlow_cur
deallocate CaseFlow_Cur--00:06:00
declare CaseFlow_cur cursor FAST_FORWARD read_only for select receive_object_id, projcode from ##CaseFlow
Open CaseFlow_cur
FETCH NEXT FROM CaseFlow_cur INTO @t_pro_id, @projcode
WHILE @@FETCH_STATUS = 0
BEGIN
select top 1 @t_t_time = isnull(t_time,0)
from szum_sase_timelimit_apply
where dept_id=@t_pro_id and projcode = @projcode
if @@rowcount = 0
begin
select @t_t_time = isnull(t_time,0) from szum_event_time
where smallclass_id = ( select smallclass_id from szum_event where fid = @projcode )
if @t_t_time = 0
set @t_t_time = -999999
end select @t_t_time = isnull(@t_t_time,0) + isnull(kc_time,0) from szum_event_time
where smallclass_id =( select smallclass_id from szum_event where fid = @projcode )update ##CaseFlow set LimitTime=@t_t_time where projcode = @projcode FETCH NEXT FROM CaseFlow_cur INTO @t_pro_id, @projcode
END
Close CaseFlow_cur
deallocate CaseFlow_Cur--00:06:00
你是说的是:
select @t_t_time = isnull(@t_t_time,0) + isnull(kc_time,0) from szum_event_time
where smallclass_id =( select smallclass_id from szum_event where fid = @projcode )
LZ的程序写的不好,所以性能糟糕。我大致看了下,建议先整理好修改数据,然后可以一次update。
不建议用游标,LZ的东西完全可以避免用游标的
我是用SQL2005的自带的开发工具.
可看看用游标和传统WHILE方式的性能对比:CURSORS BE GONE
http://blog.csdn.net/lchstudy/archive/2008/12/25/3600749.aspx