介绍
客户通常需要将数据集从源系统移动到新的目标系统，插入目标表中不存在的行，并使用新值更新已存在给定键的行。这种情况通常被称为“upsert”，如果在10秒或100秒的数千条记录上逐行执行，可能会非常耗时。通过本文所述的技术，我们已经能够在20小时到20分钟的时间内，针对一个包含3000万行的目标表，优化一个包含200万行的数据集的Azure SQL upsert。
务必记住，Azure SQL数据库提供了开箱即用的高可用性，这一点在本文中有明确描述。不同的服务层通过不同的底层实现提供了这一功能，但因此，一般来说，在Azure SQL数据库中，诸如最小日志记录、简单或大容量日志恢复模式都不可用，并且持久表上的每个操作都被完全记录。
众所周知的批处理技术可以用来最小化传统工作负载中完全记录的数据库操作的影响。
对于批量加载或批量插入等其他场景，如果与日志记录最少的本地系统相比，这可能会显著影响性能。此处记录了通用和关键任务服务层的日志生成率限制，不能跨越。
优化Azure SQL upsert方案
此示例演示如何优化特定场景，客户需要定期将大型数据集更新到Azure SQL数据库中，然后执行upsert活动，如果目标表中已经存在现有记录（按键），则修改这些记录，如果不存在，则插入这些记录。
一般来说，实现这一目标有两种主要方法：
- 在应用层的数据集上迭代，并为每一行调用一个存储的proc，该proc将根据具有特定键的记录的存在执行插入/更新操作。如果要上传的记录量相对较小，这种方法可以很好地工作，否则往返和日志写入将显著影响性能。
- 利用大容量插入技术，比如在ADO.NET中使用SqlBulkCopy类将整个数据集上载到Azure SQL数据库，然后在单个批处理中执行所有插入/更新（或合并）操作，以最小化往返和日志写入，并最大限度地提高吞吐量。这种方法可以将总体执行时间从小时减少到分/秒，即使传入的数据集由数百万条记录组成。
高效使用Azure Data Factory在Azure SQL数据库中升级
在使用Azure data Factory等数据集成服务时，通常会提供#1等现成的场景，如下所述。
相反，实现#2中描述的东西需要一些变通方法，因为它将更多地取决于具体的场景需求，这些需求可能因客户而异。
从现在开始，本文将提供一个如何实现这种场景的示例。
我们应该从一个重要的点开始：前一段提到Azure SQL数据库不可用的最小日志记录有一个例外，即当您批量加载到临时表中时。通过这种方法，对于较大的数据集，可以获得更高的数据加载吞吐量，尽管您必须始终考虑这会影响同样影响TeMPDB的其他活动（即使一般情况下，与常规工作负载相比，在不同的时间窗口上发生数据加载场景）。
在这里可以找到的文章中，建议为作业利用数据库范围的临时表，以便让多个会话访问在不同会话中创建的同一临时表。
这是正确的，但它附带了一个警告：当没有任何活动会话保留对全局临时表的引用（即创建该表的会话，或任何其他维护该表引用的会话）时，实际上会立即删除全局临时表。
当使用Azure Data Factory之类的服务来协调执行上述upsert场景所需的多个活动时，您没有一个自动构造，可以用来在组成管道的所有任务中保持会话打开，因此您需要一个简单的解决方法。
在这个代码repo中，您将发现3个独立的管道：
- “OrchestrateDataLoad”是父管道，只是调用以下两个函数的包装器。
- 一个“PrepareGlobalTempTable”，它将调用“PrepareGlobalTempTable”存储过程（请参见script.sql文件），该存储过程创建用于数据加载的“##mytemptable”全局临时表，以及一个附件控制表（“mycontroltable”），该表将用于指示整个数据加载过程的完成。调用时，存储的过程包含一个循环，该循环将定期检查控制表（保持底层会话处于活动状态），并在下一个管道结束upsert过程时退出。此管道的“等待完成”属性设置为“false”，以允许父管道继续调用后续子管道。
- “CopyAndMerge”管道使用CopyData活动将源数据批量加载到全局临时表中，然后将执行“spMergeData”存储过程，该过程有效地将数据合并到最终/持久目标表（“mytargettable”）。一旦完成，它会通过更新控制表向上一个存储的进程发回信号，表示操作已经完成。
为了避免这种轮询机制，引入了一种新的数据库范围配置，称为GLOBAL_TEMPORARY_TABLE_AUTO_DROP ，目前正在预览中。设置为OFF时，需要使用DROP TABLE语句显式删除全局临时表，或者在服务器重新启动时自动删除全局临时表。详细信息可在我们的文档中找到。
通过扩展控制表和逻辑以及管理表名等，可以很容易地改进此示例，以包括并行执行多个upsert管道并命中多个目标表的能力。
原文:https://devblogs.microsoft.com/azure-sql/optimize-azure-sql-upsert-scen…
- 登录 发表评论
- 6 次浏览