高手请进，sql server 成批更新

[COMPUTERZZZ]

我要成批更新数据库，每次更新100条，
这是我的程序，感觉效率不是很高．５万条数据４分钟都没更完，有没有更好的方法？

declare @x int
declare @y int
declare @pagecount int
declare @count int
set @count=1
set @pagecount=100
set @x=1
set @y=100

while 1=1
begin
begin tran
update a set sh_finished=1 ,sh_currstatus='结算'
from
(
select Row_Number() over(order by sh_noteno asc) as RowNumber,
sh_noteno,sh_lineno,sh_finished,sh_currstatus
from sh_repair_dtl with(nolock)
where sh_finished=0 and sh_transdate<'2009-05-01 00:00:00'
) a
where RowNumber between @x and @y
--select '@@RowCount='+Cast( @@RowCount as char(10) )
--select '@@Error='+Cast( @@Error as char(10) )
--if @@Error <> 0
--begin
-- Raiserror('Update error',16,1)
-- RollBack
-- Break
--end
--判断是否全部更新完
if @@RowCount = 0
Break
else
commit
set @count=@count+1
set @x=@x+@pagecount
set @y=@y+@pagecount
select 'X='+cast(@X as char(6) )
select 'Y='+cast(@Y as char(6) )
end

Select @count

[--小F--]

循环本来就比较慢

[SQL77]

循环更新很慢的

[COMPUTERZZZ]

那有什么好方法！因为更新的数据比较多，只能分批更新的
不然数据库会锁死的

[sdhdy]

把这块：

select Row_Number() over(order by sh_noteno asc) as RowNumber, 
sh_noteno,sh_lineno,sh_finished,sh_currstatus 
from sh_repair_dtl with(nolock) 
where sh_finished=0 and sh_transdate <'2009-05-01 00:00:00' 

放进一个临时表,然后A表和临时表关联更新试试。

[COMPUTERZZZ]

sdhdy:这样吗？
declare @x int
declare @y int
declare @pagecount int
declare @count int
set @count=1
set @pagecount=100
set @x=1
set @y=100

select Row_Number() over(order by sh_noteno asc) as RowNumber,
sh_noteno,sh_lineno,sh_finished,sh_currstatus into #tmp1
from sh_repair_dtl with(nolock)
where sh_finished=0 and sh_transdate<'2009-05-01 00:00:00'

while 1=1
begin
begin tran
update a set sh_finished=1 ,sh_currstatus='结算'
--select *
from
(
select Row_Number() over(order by sh_noteno asc) as RowNumber,
sh_noteno,sh_lineno,sh_finished,sh_currstatus
from sh_repair_dtl with(nolock)
where sh_finished=0 and sh_transdate<'2009-05-01 00:00:00'
) a INNER JOIN #tmp1 b on a.Rownumber=b.rownumber
where a.RowNumber between @x and @y
--select '@@RowCount='+Cast( @@RowCount as char(10) )
--select '@@Error='+Cast( @@Error as char(10) )
--if @@Error <> 0
--begin
-- Raiserror('Update error',16,1)
-- RollBack
-- Break
--end
--判断是否全部更新完
if @@RowCount = 0
Break
else
commit
set @count=@count+1
set @x=@x+@pagecount
set @y=@y+@pagecount
select 'X='+cast(@X as char(6) )
select 'Y='+cast(@Y as char(6) )
end

Select @count