我要成批更新数据库,每次更新100条,
这是我的程序,感觉效率不是很高.5万条数据4分钟都没更完,有没有更好的方法?declare @x int
declare @y int
declare @pagecount int
declare @count int
set @count=1
set @pagecount=100
set @x=1
set @y=100while 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) )
endSelect @count
这是我的程序,感觉效率不是很高.5万条数据4分钟都没更完,有没有更好的方法?declare @x int
declare @y int
declare @pagecount int
declare @count int
set @count=1
set @pagecount=100
set @x=1
set @y=100while 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) )
endSelect @count
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表和临时表关联更新试试。
declare @x int
declare @y int
declare @pagecount int
declare @count int
set @count=1
set @pagecount=100
set @x=1
set @y=100select 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) )
endSelect @count