利用MSSQL每次只处理一页数据(限MSSQL2005)

如果在数据量大的情况下,更新表中数据,有可能会出现超时现象;我认为可以采用批量更新的方式,一种办法就是采用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