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

2012-10-10 03:45:18 1.2k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SRAM Bank数量配置：性能、面积与功耗的权衡指南 SRAM（静态随机存取存储器）是芯片设计中的关键存储单元，其Bank数量配置直接影响访问延迟、吞吐率、功耗和面积。Bank切分通过将存储阵列分区，缩短位线和字线延迟，从而提升性能，但会增加外围电路开销。合理配置Bank数量可优化PPA（性能、功耗、面积），在28nm和16nm工艺下，2个Bank通常是性价比最高的选择。本文基于实测数据，探讨不同场景下的Bank配置策略，帮助工程师避免常见误区，提升设计效率。阅读详情

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

...全文

北航机器学习2020-2021秋季学期期末试题回忆北航机器学习2020-2021秋季学习期末试题回忆 fanjn, zhangyy, yuanq, wanggq, weixy, anh, wangyy, zhouxy, likc1 【注：考试使用纯白的A3答题纸，考试时间2h，各位同学可以自行模拟考试】此试卷1-8题为10分，第9题为20分 1.2077年人类发射近地卫星，一共发射800颗，根据大小和轨道远近，有着不同的宜居条件（+表示宜居），有以下表格（数字八九不离十，其他属性什么的原题就是交替的）数量大小轨道是否宜居 30 阅读详情

NNBBDD

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

NNBBDD 2012-10-26

打赏
举报

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

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

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

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

再来壹串 2012-10-17

打赏
举报

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

fcuandy 2012-10-16

打赏
举报

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

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

星火燎猿 2012-10-16

打赏
举报

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

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

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

menhuanxiyou 2012-10-16

打赏
举报

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

sqlfun 2012-10-15

打赏
举报

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

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

Andy-W 2012-10-15

打赏
举报

[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

gis39 2012-10-15

打赏
举报

创建ID列的索引

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

insert into A select * from B

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

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

IFocusYou 2012-10-15

打赏
举报

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

begin tran
...
commit tran

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

zhazhuzhao 2012-10-15

打赏
举报

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

sanwensanda 2012-10-15

打赏
举报

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

budong0000 2012-10-12

打赏
举报

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

nevermorewish 2012-10-11

打赏
举报

路过一下

天才在左疯子在右 2012-10-10

打赏
举报

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

DBA_磊仔 2012-10-10

打赏
举报

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

LongRui888 2012-10-10

打赏
举报

[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

SQL77 2012-10-10

打赏
举报

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

LongRui888 2012-10-10

打赏
举报

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

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

LongRui888 2012-10-10

打赏
举报

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

汤姆克鲁斯 2012-10-10

打赏
举报

路过一下

加载更多回复（17）

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

相关推荐