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

Question

[背景]
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
 

Answer 1

merge into 应该可以吧

Answer 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

Answer 3

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

Answer 4

--将中转表中已存在的数据插入到 临时表 #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

Answer 5

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

Answer 6

这种程序设计思路本来就有问题。
数据库是何等强大的一个工具，按你这种思路，只是当一个仓库用。所有数据都要读出来，在后台应用作判断，再做数据处理。你想想，对于每条数据，你要做多少次数据库交互？每次交互是不是都需要创建，关闭一次数据库联接？这能快的起来么？你这种情况，估计也是远程数据库。你要做的应该：在后台直接把数据丢给数据库，让数据库自己判断是insert还是update