core批量更新_如何解决⼤批量数据保存的性能问题
⼀、背景
明源云ERP开放平台提供了强⼤的基于实体的ORM框架:“实体服务”,它可以让开发⼈员只需专注于业务逻辑代码的编写,不⽤关⼼数据库相关的操作,⼤⼤提⾼了开发效率。
在⾏业中所有基于实体的ORM框架,均存在同⼀个问题:在批量操作数据时,性能表现乏⼒。因为在批量保存数据时,可能既包含新增的记录,⼜包含更新或删除的记录,⼀般的ORM框架必须为每⼀条记录⽣成⼀条SQL语句,然后⼀条⼀条的执⾏,这样执⾏的性能⾮常差,⽽通常的优化⽅案是将数据分批保存,但在数据量特别⼤时,保存的性能表现依然⽆法接受。在ERP的业务中,存在很多这种批量操作⼤量数据的场景,例如通过Excel批量导⼊⼤量数据,⽽且这类功能基本都出现在核⼼的业务模块,所以我们必须要攻克这个问题。
本⽂将介绍我们如何通过技术上不断的优化,最终⼤幅提升实体服务性能的过程。
⼆、初步优化
如前所述,ORM框架之所以在⼤批量数据保存时性能差,主要原因是它必须⼀条⼀条的执⾏SQL,熟悉数据库技术的同学应该清楚,每次SQL执⾏都会⽤到数据库连接,⽽频繁开启连接会给数据库造成巨⼤
压⼒。所以我们⼀开始想到的优化⽅案,就是将这批SQL⼀次性发给数据库执⾏,只开⼀次数据库连接。
我们将SQL⼀次性发给数据库执⾏,确实⼤幅减少了开启数据库连接的次数,但依然存在性能问题。经过技术分析,我们发现原因在于发给数据库执⾏的SQL语句太⼤,造成了⽹络传输延迟过⼤。⽐如本次要批量保存的数据⾏数有10万,字段超过50个,需执⾏的SQL语句就有10万条,虽然是⼀次性执⾏,但SQL本⾝可能有⼏兆。
为了解决这个问题,我们需要将10万条SQL语句合并起来,变成⼏条SQL去执⾏。
三、第⼆轮优化
我们实现合并SQL语句⽅案时,遇到的第⼀个问题是:批量保存的数据,其中每⼀⾏的状态是不⼀样的,有些⾏可能是新增,有些⾏可能是更新,有些可能是删除,不同类型的数据如何合并SQL?
解决⽅案:将新增、更新、删除三种类型的数据,分别合并成三条不同形式的SQL语句进⾏执⾏。
我们紧接着遇到了第⼆个问题:要更新的每⼀⾏数据中,更新的字段也不⼀样,可能第⼀⾏要更新A字段,第⼆⾏要更新B字段,每⼀⾏的Update语句都不⼀样,如何合并成⼀条SQL?
解决⽅案:不管每⼀⾏要更新哪些字段,我们合并后的SQL都更新所有字段,然后再使⽤⼀个标识字段表⽰该字段是否需要插⼊或更新,这样就能⽤同⼀条SQL更新所有⾏。
下⾯我们以批量保存⽤户表MyUser为例,分别介绍在新增、更新、删除场景下的SQL如何合并。
MyUser表的结构如下:
待保存的记录数据如下:
1、新增场景
核⼼思路:通过参数化和“Select”的SQL语句,⼀次性将要新增的记录插⼊MyUser表(如果没有值,则使⽤字段的默认值代替)。合并后的SQL语句如下:
2、更新场景
核⼼思路:通过判断字段值是否更新,动态拼接Set语句的右值。
合并后的SQL语句如下:
3、删除场景
核⼼思路:通过In⼦查询,根据ID批量删除记录。
合并后的SQL语句如下:
通过上述优化⽅法,SQL语句本⾝⼤幅缩减,但依然存在两个问题:
合并后的SQL⽤到参数化,但数据库限制参数最多2100个,如果待保存的数据⾏数过多时,会超出参数个数限制;
由于⼦查询的语句长度会随着记录增⼤⽽增加,数据量特别⼤时,产⽣的SQL语句依然很⼤,依然会存在性能隐患。
四、第三轮优化
为了解决这两个问题,我们通过SqlBulkCopy机制,快速的将待保存数据⼀次性插⼊到数据库的临时表,SQL语句通过关联临时表进⾏批量新增、修改、删除,这样SQL语句本⾝可以变得更加简短,⽽且因为去掉了参数化查询,也规避了参数个数限制的问题。(Microsoft SQL Server中提供了⼤容量复制程序实⽤⼯具 (bcp),⽤于快速将⼤型⽂件批量复制到SQL Server数据库中的表或视图中。在ADO.Net 中,SqlBulkCopy类提供了托管代码的解决⽅案,⽀持类似的功能。)
我们的具体做法如下:
1. 在数据库中创建与⼦查询同结构的临时表,⽐如⽤户表,可以创建对应的临时表#temp_myUser;
2. 使⽤SqlBulkCopy将所有待保存的数据⼀次性插⼊到临时表中;
3. 合并后的SQL通过关联临时表,将数据保存到业务表中;
4. 删除临时表,完成数据批量操作。
通过关联临时表合并成的最终SQL语句如下:
1、新增场景:
批量更新sql语句2、更新场景:
3、删除场景:
五、优化前后性能⽐对结果
我们将优化前与优化后进⾏性能⽐对,在⼀个50个字段的表中分别插⼊、修改数据,输出使⽤的时间。
数据新增场景性能⽐对:
数据修改场景性能⽐对:
优化结果:新增或更新3万⾏记录,优化前需2分钟左右,优化后仅需6秒左右,性能提升近20倍。
六、总结
我们从这次的性能优化中,得到了两点收获:
平台的实体服务层再次体现了其优越性。正是因为业务数据的增、改、删操作都被实体服务接管,平台才能进⾏统⼀和彻底的性能优化。
进⼊“深⽔区”的突破更加难能可贵。ORM框架很多⼈都会⽤,但真正⽤到“深⽔区”,并且在“深⽔区”突破⼀些难点问题,就需要我们不断的探索和钻研了。
如果⼤家对ORM相关的数据访问技术有⾃⼰的⼼得或疑惑,欢迎在我们留⾔交流。------ END ------
作者简介
唐同学: 架构师,⽬前负责ERP运⾏平台整体架构设计和开发。
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论