[真心求助]SQL_SERVER中如何能够同时快速的更新和插入数据

[飞鱼翔月]

[背景]
SQL Server版本：2008以上
数据库信息：1个数据库，库中大概100万条数据上下，数据库大概有100+字段，其中有A、B、C和Z四个字段在处理中需要用到，字段都是字符串类型。

[操作]
1.使用python对这个数据库进行插入或者更新操作。
2.每次插入或者更新的数据量大概在10000-30000条，算法如下：
比对新插入的数据，看A、B、C三个字段在数据库中是否已经有数据存在
1）如果存在，那么将新数据中的Z根据老数据做一些运算处理（这里什么处理可以不用关心），然后将数据更新到数据中（每个字段都更新）
2）如果源数据库中找不到A、B、C和新数据都相同的数据，那么插入新数据，所有字段都不做修改。

以上就是全部的诉求，我原先的实现是最简单的遍历，方法如下：
[实现算法]
1.循环遍历每条要准备新插入的数据：
使用SELECT...WHERE查看数据的A、B、C是否存在：
1）如果存在，那么参照存在的数据对Z值进行修改，之后使用UPDATE XXX SET XXX=XXX WHERE语句对数据库进行更新
2）如果不存在，那么使用INSERT INTO XXX (XXX,XXX,XXX) VALUES(XXX,XXX,XXX)的SQL语句插入数据库

这个算法可以正常工作，但是太慢了。。。python上面调试下来每秒钟最多才能插入2-3条数据，插入30000条数据要几个小时（崩溃+泪奔。。。）

求教大神有没有更好的办法，或者说这个数据库结构方面还可以有怎么样的优化，小白谢过了。。。orz

[砸死牛顿的苹果]

merge into 应该可以吧

[吉普赛的歌]

1. 原始表创建索引， 单独执行： create index ix_xxx_a_b_c on xxx(a,b,c) 2. 创建一个中转表, 表结构完全同原始表一样 create table tmp( a varchar(50), b varchar(50), c varchar(50), ... ) go --创建中间表上的索引 create index ix_xxx_a_b_c on xxx(a,b,c) 3. 将需要插入或更新时，可以这么操作： -- 3.1 清空中转表： truncate table tmp; -- 3.2 将新数据一次性插入到上一步的中间表 tmp; -- 3.3 批量插入 insert into xxx(a,b,c,...) select a,b,c,... from tmp as x where not exists( select 1 from xxx as y where x.a=y.a and x.b=y.b and x.c=y.c ) -- 3.4 批量更新 update x from xxx as x inner join tmp as y on x.a=y.a and x.b=y.b and x.c=y.c

[飞鱼翔月]

引用 1 楼 砸死牛顿的苹果 的回复:

merge into 应该可以吧

我的同事也提到了这个命令，具体怎么操作呢？是创建中转表么？

[飞鱼翔月]

引用 2 楼 吉普赛的歌 的回复:

1. 原始表创建索引， 单独执行： create index ix_xxx_a_b_c on xxx(a,b,c) 2. 创建一个中转表, 表结构完全同原始表一样 create table tmp( a varchar(50), b varchar(50), c varchar(50), ... ) go --创建中间表上的索引 create index ix_xxx_a_b_c on xxx(a,b,c) 3. 将需要插入或更新时，可以这么操作： -- 3.1 清空中转表： truncate table tmp; -- 3.2 将新数据一次性插入到上一步的中间表 tmp; -- 3.3 批量插入 insert into xxx(a,b,c,...) select a,b,c,... from tmp as x where not exists( select 1 from xxx as y where x.a=y.a and x.b=y.b and x.c=y.c ) -- 3.4 批量更新 update x from xxx as x inner join tmp as y on x.a=y.a and x.b=y.b and x.c=y.c

看上去可行，我去操作一下~

[飞鱼翔月]

引用 2 楼 吉普赛的歌 的回复:

1. 原始表创建索引， 单独执行： create index ix_xxx_a_b_c on xxx(a,b,c) 2. 创建一个中转表, 表结构完全同原始表一样 create table tmp( a varchar(50), b varchar(50), c varchar(50), ... ) go --创建中间表上的索引 create index ix_xxx_a_b_c on xxx(a,b,c) 3. 将需要插入或更新时，可以这么操作： -- 3.1 清空中转表： truncate table tmp; -- 3.2 将新数据一次性插入到上一步的中间表 tmp; -- 3.3 批量插入 insert into xxx(a,b,c,...) select a,b,c,... from tmp as x where not exists( select 1 from xxx as y where x.a=y.a and x.b=y.b and x.c=y.c ) -- 3.4 批量更新 update x from xxx as x inner join tmp as y on x.a=y.a and x.b=y.b and x.c=y.c

有个问题，在update之前，我是需要批量对数据库中已存在的数据的字段z进行更新的，这个操作什么时候来做呢？

[吉普赛的歌]

引用 5 楼 飞鱼翔月 的回复:

有个问题，在update之前，我是需要批量对数据库中已存在的数据的字段z进行更新的，这个操作什么时候来做呢？

--将中转表中已存在的数据插入到 临时表 #tmp2
select y.* 
into #tmp2 
from xxx as x inner join tmp as y 
    on x.a=y.a and x.b=y.b and x.c=y.c

--临时表加索引
create clustered index ix_#tmp2 on #tmp2(a,b,c)

--如果有已存在数据
if exists(select 1 from tmp2)
begin
    --更新临时表中的 z 
    update tmp2 set z = ?
    --更新原始表
    update x
    set c1=?,c2=?   --列名自己写
    from xxx as x inner join #tmp2 as y 
    on x.a=y.a and x.b=y.b and x.c=y.c
end

--找到在中转表中的，但不在临时表中的数据（也就是真正的新数据）
--插入到原始表
insert into xxx(a,b,c,...)
select a,b,c,...
from tmp as x left join #tmp2 as y
on x.a=y.a and x.b=y.b and x.c=y.c
where y.a is null

[hgwyl]

这个是不是应该把需要对比数据的列，做一下索引（看具体数据的情况）？

[hgwyl]

100万数据，要加快的话，减少检索数据量——我觉得这个是最重要的

[Apq001]

中转表是肯定要的,索引也是要的,最后如果还要考虑到大事务对系统整体的影响的话,那么就分批MERGE,比如,每次只MERGE 5000 行
整个过程可不要再套事务,分批的目的就是减少事务大小,如果循环外面加事务,那就等于没搞了

[shoppo0505]

这种程序设计思路本来就有问题。
数据库是何等强大的一个工具，按你这种思路，只是当一个仓库用。

所有数据都要读出来，在后台应用作判断，再做数据处理。你想想，对于每条数据，你要做多少次数据库交互？每次交互是不是都需要创建，关闭一次数据库联接？这能快的起来么？你这种情况，估计也是远程数据库。

你要做的应该：在后台直接把数据丢给数据库，让数据库自己判断是insert还是update