如果在数据量大的情况下,更新表中数据,有可能会出现超时现象;我认为可以采用批量更新的方式,一种办法就是采用row_number分页机制进行更新
Create procedure temp_anny_filter_update
@pagenum int,
@pagelen int
as
begin
set nocount on
declare @filterList table
(
id bigint,
counter int,
rownum int
)
declare @pagecount int
declare @rows int
with filter_list_seq(id,counter,rownum)
as
(
select b.id, b.counter,
row_number() over (order by b.date_sent desc) as rownum
FROM filter b with(nolock) inner join usermaster a with(nolock)
on a.usermaster_id = b.usermaster_id
WHERE a.usermaster_type = 1
and a.status = 1
and b.interval_days > 0
and convert(varchar,isnull(b.date_sent,'1900-9-9') + b.interval_days ,102) <= convert(varchar,getdate(),102)
--and a.email ='anny.chen@zpdev.com.cn'
),
filter_list_total(id,counter,recordcount)
as
(
select null, 0, count(*) from filter_list_seq
)
--将一页数据写入零时表
insert into @filterList(id,counter,rownum)
select id,counter,rownum
from filter_list_seq
where rownum between ((@pagenum-1) * @pagelen+1) and (@pagenum * @pagelen)
union
select id,counter,recordcount from filter_list_total;
--select @pagecount = rownum from @filterList where id is null
--获取零时表记录数
select @rows=Count(*) from @filterList where id is not null
--更新零时表中一页数据
UPDATE filter SET date_sent=getdate(),counter=isnull(b.counter,0) + 1
FROM filter b,@filterList a
where b.id = a.id and a.id is not null
--直到记录数据等于零,数据才全部更新完
if @rows <> 0
begin
exec temp_anny_filter_update @pagenum,@pagelen
end
set nocount off
end
使用递归影响效率
ALTER procedure [temp_anny_filter_update]
@pagenum int,
@pagelen int,
@recordcount int output,
@pagetotalcount int output,
@RetRows int output
as
begin
set nocount on
declare @filterList table
(
id bigint,
counter int,
rownum int
)
declare @rows int
with filter_list_seq(id,counter,rownum)
as
(
select b.id, b.counter,
row_number() over (order by b.date_sent desc) as rownum
FROM filter b with(nolock) inner join usermaster a with(nolock)
on a.usermaster_id = b.usermaster_id
WHERE a.usermaster_type = 1
and (a.status = 1 or a.status =2)
and (b.filter_type = 1 or b.filter_type = 3)
and b.interval_days > 0
and convert(varchar,isnull(b.date_sent,'1900-9-9') + b.interval_days ,102) <= convert(varchar,getdate(),102)
--and a.email ='anny.chen@zpdev.com.cn'
),
filter_list_total(id,counter,recordcount)
as
(
select null, 0, count(*) from filter_list_seq
)
insert into @filterList(id,counter,rownum)
select id,counter,rownum
from filter_list_seq
where rownum between ((@pagenum-1) * @pagelen+1) and (@pagenum * @pagelen)
union
select id,counter,recordcount from filter_list_total;
select @recordcount=rownum from @filterList where id is null
UPDATE filter SET date_sent=getdate(),counter=isnull(b.counter,0) + 1
FROM filter b,@filterList a
where b.id = a.id and a.id is not null
set @RetRows = @@Rowcount
--总页数
set @pagetotalcount = (case when ((@recordcount % @pagelen)=0) then (@recordcount / @pagelen)
else ((@recordcount / @pagelen)+1) end)
set nocount off
end
