2023年7月20日发(作者：)

.netcore批量更新_如何解决⼤批量数据保存的性能问题⼀、背景明源云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语句本⾝可以变得更加简短，⽽且因为去掉了参数化查询，也规避了参数个数限制的问题。(MicrosoftSQL Server中提供了⼤容量复制程序实⽤⼯具 (bcp)，⽤于快速将⼤型⽂件批量复制到SQL Server数据库中的表或视图中。在中，SqlBulkCopy类提供了托管代码的解决⽅案，⽀持类似的功能。)我们的具体做法如下：1. 在数据库中创建与⼦查询同结构的临时表，⽐如⽤户表，可以创建对应的临时表#temp_myUser；2. 使⽤SqlBulkCopy将所有待保存的数据⼀次性插⼊到临时表中；3. 合并后的SQL通过关联临时表，将数据保存到业务表中；4. 删除临时表，完成数据批量操作。通过关联临时表合并成的最终SQL语句如下：1、新增场景：2、更新场景：3、删除场景：五、优化前后性能⽐对结果我们将优化前与优化后进⾏性能⽐对，在⼀个50个字段的表中分别插⼊、修改数据，输出使⽤的时间。数据新增场景性能⽐对：数据修改场景性能⽐对：优化结果：新增或更新3万⾏记录，优化前需2分钟左右，优化后仅需6秒左右，性能提升近20倍。六、总结我们从这次的性能优化中，得到了两点收获：平台的实体服务层再次体现了其优越性。正是因为业务数据的增、改、删操作都被实体服务接管，平台才能进⾏统⼀和彻底的性能优化。进⼊“深⽔区”的突破更加难能可贵。ORM框架很多⼈都会⽤，但真正⽤到“深⽔区”，并且在“深⽔区”突破⼀些难点问题，就需要我们不断的探索和钻研了。如果⼤家对ORM相关的数据访问技术有⾃⼰的⼼得或疑惑，欢迎在我们公众号留⾔交流。------ END ------作者简介唐同学： 架构师，⽬前负责ERP运⾏平台整体架构设计和开发。