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

[NNBBDD]

两张表的字段完全一样，一张是临时表，一张是正式表，现在需要做的是，先把临时表里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的时候，执行这个存储过程相当耗时，造成数据库的阻塞，完全接受不了，所以请教大家帮忙优化一下，或者能给一个更好的思路，不胜感激、、、、、、

[汤姆克鲁斯]

路过一下

[LongRui888]

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

[LongRui888]

你调用的存储过程的其实就是临时表中的id如果在主表中有，那么就更新主表中的值，如果没有，那么就插入到主表，然后把临时表的记录删除，临时表的id可以建立一个索引，这样至少能保证只锁定需要查询的id对应的记录。

如果可以的话，你可以把调用的存储过程里面的条件判断和主要的update语句、insert语句贴出来，这样大家才能更加仔细的分析问题。

[SQL77]

楼主请详细描述一下你的业务流程。
2W条不算多啊。

[LongRui888]

[Quote=引用 19 楼 的回复:]
merge不一定管用，merge会用全表扫描的，数据量不大，照理应该不会那么慢吧，可能是和阻塞有关，如果你在update主表的时候，正好有人在查这个表，并且查询的id正好是你需要update的id，那么就算按照id建了索引，select查询对于这个id所对应的记录持有的锁，也是在语句运行完成之后才会释放，那样肯定会导致update语句变慢。

你可以用这个语句查一下是不是有等待：

sel……
[/Quote]
我上面写的有问题，改一下

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

[DBA_磊仔]

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

[天才在左疯子在右]

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

[nevermorewish]

路过一下

[budong0000]

路只有一条，分区啊，哥哥。

[sanwensanda]

可不可以这样，临时表分为两个表，一个用于更新，一个用于插入，这样数据量少了，并且可以分开执行。

[zhazhuzhao]

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

[IFocusYou]

如果你是在循环中做这些操作，数据量稍大肯定会慢。把操作放到一个事务中，可以减少日志操作。会快一些。

begin tran
...
commit tran

看不代码，我只能提供这些信息了。

[gis39]

创建ID列的索引

delete from A
where exists (select 1 from B where A.id=B.id)

insert into A select * from B

然后删除表数据，删除表，这个会吧

可以中间提交一次以减少缓冲空间的使用。

[Andy-W]

[Quote=引用楼主 的回复:]
两张表的字段完全一样，一张是临时表，一张是正式表，现在需要做的是，先把临时表里id和正式表里id相同的列全部更新到正式表，
SQL code

update A set A.1 = A.1+B.1,A.2 = A.2+B.2,... from B where A.id=B.id



然后再把临时表里这些数据删除掉，剩下的就全部insert into到正式表，然后清空临时表，这个存储过程……
[/Quote]

可能是存儲過程寫法有問題，可採用分批提交，特別是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_B

create 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 off

go

-- 存儲過程內容：
if object_id('up_MoveData_BtoA') is not null drop proc up_MoveData_BtoA
go
create proc up_MoveData_BtoA 
As

declare @batch int=2000, --每2000行提交一次，可以根據實際服務器來調整
		@For int=1
While 1=1
begin

if 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

[sqlfun]

1、因为也不知道LZ的实际业务情况如何，因此建议先确认下存储过程的性能瓶颈是在哪里？是update，还是delete部分？这个可以使用LS各位提供的一些方法来确定。目前来看，可能性最大的就是update了，但update为什么会慢？主要原因是否由于锁等待引起的，如果是的话，那说明你这张表未来会是一个性能热点。从表设计或者代码调整的角度看有没有调整的余地？

2、从你的原始需求来看，是因为原来更新的太频繁，所以才采取了临时表的方案。这应该也算是数据库里面的一个典型场景了。对于这种情况，可以考虑楼上有人提到的分区，当然这是根据你的实际业务来做出判断的。另外就是调整下索引，尽量保证索引的轻量性。

[menhuanxiyou]

这个东西开发必须会？还是专门高数据库的人会就可以了?

[星火燎猿]

[Quote=引用 33 楼 的回复:]

1、因为也不知道LZ的实际业务情况如何，因此建议先确认下存储过程的性能瓶颈是在哪里？是update，还是delete部分？这个可以使用LS各位提供的一些方法来确定。目前来看，可能性最大的就是update了，但update为什么会慢？主要原因是否由于锁等待引起的，如果是的话，那说明你这张表未来会是一个性能热点。从表设计或者代码调整的角度看有没有调整的余地？

2、从你的原始需求来看，是因为原来……
[/Quote]顶...

[fcuandy]

你自己的测试情况不足， 无法给出合理建议。

通常我们认为一个批次的作业性能是最好的， 但是并发情况下由于单批次的大操作引起的IO峰值影响其它进程或被其它进程影响容易造成阻塞. 分批次操作可以减少或避免这种影响，但是有可能的代价就是增加处理时间。
需要你自己做详细的测试，然后找到平衡点，最终给出合理的优化方案。

[再来壹串]

习惯先看执行计划，然后根据执行计划给出的结果进行优化，数据量大分多次处理

[NNBBDD]

[Quote=引用 35 楼 的回复:]
你自己的测试情况不足， 无法给出合理建议。

通常我们认为一个批次的作业性能是最好的， 但是并发情况下由于单批次的大操作引起的IO峰值影响其它进程或被其它进程影响容易造成阻塞. 分批次操作可以减少或避免这种影响，但是有可能的代价就是增加处理时间。
需要你自己做详细的测试，然后找到平衡点，最终给出合理的优化方案。
[/Quote]

不好意思，之前几天在忙另外一个项目，这个地方就被搁置下了，现在回来了，这个问题依然没有解决，感谢大家的讨论，现在我把我流程说得详细一点：

现在要实现的是个统计功能，在程序中将数据统计好，然后批量提交到数据库中相应的表里，一共有8个表，程序中是每1W条提交一次，提交到临时表中，然后调用存储过程，将临时表中的数据更新到主表中。现在的问题就是，当程序执行后，这个将临时表的数据提交到主表中的存储过程会占用相当长一的时间，我这段时间也测试了下，更新确实很慢，更新过后的删除也会很慢，因为我现在采用的是更新过后 就把更新的数据全删除，临时表就全剩下插入的，插入后再把表清空，下一个1w条的时候又重复这样的过程。我在数据库方面不是很精通，只能想到这样的笨办法。我想现在可能存在的问题就是，因为有8 个表，每个表都要更新的，所以占用的时间就长了。请问大家有什么好的办法吗？