两表更新，请教大家优化方法，绝对挑战你的极限

Question

两张表的字段完全一样，一张是临时表，一张是正式表，现在需要做的是，先把临时表里id和正式表里id相同的列全部更新到正式表，update A set A.1 = A.1+B.1,A.2 = A.2+B.2,... from B where A.id=B.id 
然后再把临时表里这些数据删除掉，剩下的就全部insert into到正式表，然后清空临时表，这个存储过程是一直循环执行的，而且还有好几个类似这样的存储过程，
   现在我的问题是，当临时表的数据量大于2W的时候，执行这个存储过程相当耗时，造成数据库的阻塞，完全接受不了，所以请教大家帮忙优化一下，或者能给一个更好的思路，不胜感激、、、、、、

Answer 1

1  ID做主键
2  insert 到正式表
3  update 到正式表
没有条件.

Answer 2

临时表的数据 比较大的时候考给临时表加索引最好是使用profile去跟踪一下 那部分于语句的开销大在下手优化一般过滤条件设置的是cpu >= 500, duration >=200
这是比较严格一点的, 如果这样的很多, 可以再把值设置高一些

Answer 3

我有个疑问，你这思路不就是把临时表的数据全表更新到正式表吗？直接truncate 正式表，然后把临时表的insert进去不就醒咯？

Answer 4

哦，你是增量的，那不能truncate，你这个接受几乎不能并发操作吗？

Answer 5

ID都是主键，两个表也都有索引，正式表是唯一索引，临时表不唯一；
你说的是先insert后，剩下的就直接更新，没有条件了？没太明白，没条件是什么意思？就算临时表里只剩下更新的数据，那也得有个where A.id=B.id才行吧？

Answer 6

高级的SQLServer可以使用过滤索引

Answer 7

用merge into 语法,相同的更新，不同的插入正式表，再truncate table 临时表
delete也是很耗时的。
MSSQL 2008支持merge into

Answer 8

恩，之所有要建个临时表，就是想实现批量操作，之前我的做法是来一条数据就直接update，结果因为数据量很大，造成数据库一直死锁，现在我想的是加个临时表，中间过渡一下，反正最终目就是要更新到正式表

Answer 9

嗯，没错，insert没有条件，正式表有的ID，插入会失败
update 还是需要条件

Answer 10

临时表做索引没，分析下是update关联慢，还是insert 慢，最好有执行计划展示出来

Answer 11

2005以后有分区表啊。2008才有merge

Answer 12

merge不一定管用，merge会用全表扫描的，数据量不大，照理应该不会那么慢吧，可能是和阻塞有关，如果你在update主表的时候，正好有人在查这个表，并且查询的id正好是你需要update的id，那么就算按照id建了索引，select查询对于这个id所对应的记录持有的锁，也是在语句运行完成之后才会释放，那样肯定会导致update语句变慢。你可以用这个语句查一下是不是有等待：select r.session_id,
       r.command,
       r.blocking_session_id,   --阻塞当前会话的会话id
       r.wait_type,             --等待类型
       r.wait_time,             --等待时间
       r.last_wait_type,        --最后等待类型
       r.wait_resource,
       r.open_transaction_count --打开事务数
from sys.dm_exec_requests r
where @@SPID = 会话id

Answer 13

你调用的存储过程的其实就是临时表中的id如果在主表中有，那么就更新主表中的值，如果没有，那么就插入到主表，然后把临时表的记录删除，临时表的id可以建立一个索引，这样至少能保证只锁定需要查询的id对应的记录。如果可以的话，你可以把调用的存储过程里面的条件判断和主要的update语句、insert语句贴出来，这样大家才能更加仔细的分析问题。

Answer 14

我上面写的有问题，改一下select r.session_id,
  r.command,
  r.blocking_session_id, --阻塞当前会话的会话id
  r.wait_type, --等待类型
  r.wait_time, --等待时间
  r.last_wait_type, --最后等待类型
  r.wait_resource,
  r.open_transaction_count --打开事务数
from sys.dm_exec_requests r
where r.session_id = 调用存储过程的会话id

Answer 15

插入9W条数据用八秒我认为不是特别的慢，查9W条数都要几秒的，
如果非要加快它，需要提升硬件方面的内容，比如说做表分区，
映射到几个不同的磁盘，分摊IO的开销，但是这个分区函数你不好找

Answer 16

为什么会出现在这种情况呢，是否开始设计都有问题？
 为什么不将正式表A和临时表B 的数据查询出来insert 第三者表C中呢？

Answer 17

不用临时表了，用2个实体表代替临时表，实体表t1,t2;每次插入的时候注意t1表中的数据量，如果超过2w后，往t2表中插数据；然后把t1的往主表中更新导入；这样可以将影响时间控制好；一句话，加入缓冲带。

Answer 18

如果你是在循环中做这些操作，数据量稍大肯定会慢。把操作放到一个事务中，可以减少日志操作。会快一些。begin tran
...
commit tran看不代码，我只能提供这些信息了。

Answer 19

创建ID列的索引delete from A
 where exists (select 1 from B where A.id=B.id)insert into A select * from B 然后删除表数据，删除表，这个会吧可以中间提交一次以减少缓冲空间的使用。

Answer 20

可能是存儲過程寫法有問題，可採用分批提交，特別是10W以上都需要考慮分批提交數據，可參考：
use tempdb
go
--創建測試表
if object_id('Tb_A') is not null drop table Tb_A
if object_id('Tb_B') is not null drop table Tb_Bcreate table Tb_A(ID int primary key ,c2 nvarchar(50),c3 nvarchar(50)) --ID作為主鍵
create table Tb_B(ID int primary key ,c2 nvarchar(50),c3 nvarchar(50)) --ID作為主鍵
go--批量處理使用該字段控制;
alter table Tb_B add Col_Identity int identity(1,1) 
go--插入測試數據
set nocount on
insert into Tb_A(ID,c2,c3) values(1,'c1','c2'),(2,'c3','c4'),(3,'c5','c6')  
insert into Tb_B(ID,c2,c3) values(2,'c3_a','c4_a'),(3,'c5_b','c6_b'),(4,'c7','c8')  
set nocount offgo-- 存儲過程內容：
if object_id('up_MoveData_BtoA') is not null drop proc up_MoveData_BtoA
go
create proc up_MoveData_BtoA 
Asdeclare @batch int=2000, --每2000行提交一次，可以根據實際服務器來調整
@For int=1
While 1=1
beginif Not Exists(select 1 from dbo.Tb_B) break;Begin Try
Begin Transaction

update a
set a.c2=b.c2,a.c3=b.c3
from Tb_A a
inner join  Tb_B b on b.ID=a.ID
and b.Col_Identity between (@For-1)*@batch+1 And @For*@batch

insert into dbo.Tb_A ( ID, c2, c3 )
select ID,c2,c3  from dbo.Tb_B a where not exists(select 1 from dbo.Tb_A y where y.ID=a.ID) and a.Col_Identity between (@For-1)*@batch+1 And @For*@batch

delete Tb_B where Col_Identity between (@For-1)*@batch+1 And @For*@batch

set @For+=1;
Commit Transaction
End Try
Begin Catch
Declare @Error nvarchar(2047)
Set @Error=Error_message()
Raiserror 50001 @Error
Rollback Transaction
goto ExitFlag
End Catch
end 
ExitFlag:
go
--調用存儲過程：
exec up_MoveData_BtoA
go

Answer 21

1、因为也不知道LZ的实际业务情况如何，因此建议先确认下存储过程的性能瓶颈是在哪里？是update，还是delete部分？这个可以使用LS各位提供的一些方法来确定。目前来看，可能性最大的就是update了，但update为什么会慢？主要原因是否由于锁等待引起的，如果是的话，那说明你这张表未来会是一个性能热点。从表设计或者代码调整的角度看有没有调整的余地？2、从你的原始需求来看，是因为原来更新的太频繁，所以才采取了临时表的方案。这应该也算是数据库里面的一个典型场景了。对于这种情况，可以考虑楼上有人提到的分区，当然这是根据你的实际业务来做出判断的。另外就是调整下索引，尽量保证索引的轻量性。