【求助】优化更新海量数据

[hjzdhr]

假设我要查询A数据库中的a表数据，用它来更新B数据库中的b表。

a表中要查询 a1, a2, num, status 4列。

b表中主要更新status这列。 通过a.a1 = b.a1 AND a.a2 = b.a2 来查找。 但B表中的数据跟num的数量对应，举个例子 ：

a表中一列

a1 a2 num status
xx, yy, 3, P

b表中的查询结果是：

a1 a2 num status

xx, yy, 1, S
xx, yy, 2, Q
xx, yy, 3, T

要求把b表中num是3的status更新为a表中查询的P， 其余小于3的都更新为空（''）

我现在是用存储过程做的，先建个临时表，把A表中查询的数据存入临时表，在对临时表做循环，逐条更新， 但速度超慢，我目前测试一个月的数据，大约10万条，需要18，9分钟才能跑完。

请各位高人帮帮忙，指点一下迷津啊。 跟什么锁有关系么？

[7761098]

[Quote=引用 2 楼 liangck 的回复:]
SQL codeUPDATE BSET
status=CASEWHEN B.num< A.numTHEN''ELSE A.statusENDFROM tb1AS AJOIN tb2AS BON A.a1= B.a1AND A.a2= B.a2;
[/Quote]
这样就可以了，更新10万条不用1分钟，干吗要循环啊

[华夏小卒]

if object_ID('ta') IS NOT NULL DROP TABLE ta
go
create table  ta(a1 varchar(10), a2 varchar(10), num varchar(10), status  varchar(10))
go  
insert ta select
'xx', 'yy',  3,    'P'  union all select 
'sa', 'pwd', 4,    'sa' 

if object_ID('tb') IS NOT NULL DROP TABLE tb
go
create table  tb(a1 varchar(10), a2 varchar(10), num varchar(10), status  varchar(10))
go  
insert tb select
'xx', 'yy',  1,    'S' union all select 
'xx', 'yy',  2,    'Q' union all select 
'xx', 'yy',  3,    'T' union all select 
'sa', 'pwd', 1,    'da' union all select 
'sa', 'pwd', 2,    'sf' union all select 
'sa', 'pwd', 3,    'g' union all select 
'qq', 'pwd', 3,    'g' union all select 
'sa', 'pwd', 4,    't' 

update tb
set status=case when b.num=a.num then a.status else '' end
from tb b,ta a
where a.a1=b.a1 and a.a2=b.a2


select * from tb
a1         a2         num        status
---------- ---------- ---------- ----------
xx         yy         1          
xx         yy         2          
xx         yy         3          P
sa         pwd        1          
sa         pwd        2          
sa         pwd        3          
qq         pwd        3          g ---这一条不更新
sa         pwd        4          sa

(8 行受影响)

[bancxc]

update B..b set B..b.status=A..a.status from A..a inner join B..b on A..a.a1 = B..b.a1  AND A..a.a2 = B..b.a2 And A..a.num=B..b.num

[华夏小卒]

可以在b数据库里做个A库表的视图，

根据视图来更新

[bancxc]

update B..b set B..b.status=A..a.status from A..a inner join B..b on A..a.a1 = B..b.a1  AND A..a.a2 = B..b.a2 And A..a.num=B..b.num

[bancxc]

--create database A
--go
use A 
go
if object_id('A') is null
	create table a(a1 nvarchar(20),a2 nvarchar(20),num nvarchar(20),status nvarchar(20))
go
delete from a
insert into a values('xx','yy','3','P')
select * from a
go


--create database B
--go
use B 
go
if object_id('B') is null
	create table B(a1 nvarchar(20),a2 nvarchar(20),num nvarchar(20),status nvarchar(20))
go
delete from b
insert into B
select 'xx', 'yy',  '1',    'S' union all select 
'xx', 'yy',  '2',    'Q'  union all select 
'xx', 'yy',  '3',    'T' 
go
select * from B

update B..b set B..b.status=A..a.status from A..a inner join B..b on A..a.a1 = B..b.a1  AND A..a.a2 = B..b.a2 And A..a.num=B..b.num

[hjzdhr]

恩 正在测试二楼的那个方法，刚测完2万条， 狠快。。。

继续测10万条

[Q315054403]

有偿专业支持

[bdx808]

纯顶

[hjzdhr]

[Quote=引用 12 楼 luckyrandom 的回复:]
有偿专业支持
[/Quote]

太不厚道 这里是CSDN 广告的闪

[hjzdhr]

十一假期归来，还需要继续解决问题啊。

二楼的方法我第一次测试2万条比较快，但之后测同样的数据又变的很慢，不知道为什么。

现在测试3000多条数据就需要5分钟，还是没法用啊。

求助求助！！！

[7761098]

清空日志，清理tempdb
你B表大概有多少数据呢，如果实在太大的话考虑分批做
每次50万条这样的更新，根据你的业务逻辑，分批应该没什么影响的

[hjzdhr]

我有一年的数据要做，按每月分批处理，每月A表大约10万条， B表至少是A表的2—3倍。

现在20万左右的需要跑20分钟左右，还有办法再优化速度么？

[hjzdhr]

继续顶 高人来指点啊

[fuxiaoyang13]

帮顶！！！

[wen_880212]

update b set status = case when a.num = b.num and a.num >3 then b.status else '' end from a,b where a.a1 = a.a1 and b.a2 = b.a2

[小宏]

关注下。。。

[wuhuzhangwei]

关注下。。。

[luoyoumou]

----咋还不结帖呢？ 2楼小梁，N年前就帮你解决了！

[haitao]

b表有 a1+a2+num 的索引吗？
a表最好有 a1+a2 的索引吗？

感觉这个sql不算复杂