如何使用批处理来提升 Azure SQL 数据库和 Azure SQL 托管实例应用程序的性能

适用于：Azure SQL 数据库Azure SQL 托管实例

对 Azure SQL 数据库和 Azure SQL 托管实例执行批处理操作可以大幅改善应用程序的性能和缩放性。 为了帮助你了解优点，本文的第一部分包含一些示例测试结果，用于比较对 Azure SQL 数据库或 Azure SQL 托管实例中的数据库发出的顺序请求和分批请求。 本文的余下部分介绍了帮助你在 Azure 应用程序中成功使用批处理的方法、方案和注意事项。

为什么批处理对 Azure SQL 数据库和 Azure SQL 托管实例很重要？

对远程服务的批处理调用是提高性能和可伸缩性的常用策略。 对于任何与远程服务的交互（如序列化、网络传输和反序列化），都有固定的处理开销。 将很多单独的事务打包为一个批处理操作可最大限度降低这些成本。

在本文中，我们将深入了解各种批处理策略和方案。 尽管这些策略对于使用 SQL Server 的本地应用程序也很重要，但是将批处理用于 Azure SQL 数据库和 Azure SQL 托管实例主要是基于以下几个原因：

• 访问 Azure SQL 数据库和 Azure SQL 托管实例时，网络延迟可能会更大，尤其是在从同一Microsoft Azure 数据中心外部访问 Azure SQL 数据库或 Azure SQL 托管实例时。
• Azure SQL 数据库和 Azure SQL 托管实例的多租户特征意味着数据访问层的效率与数据库的总体缩放性有关。 在响应使用量超过预定义的配额时，Azure SQL 数据库和 Azure SQL 托管实例可减小吞吐量或引发限制异常。 一些提高效率的措施（如批处理），允许你在达到这些配额前做更多的工作。
• 批处理对于使用多个数据库或联合的体系结构也很有效（分片）。 与每个数据库单位的交互效率仍是影响总体伸缩性的关键因素。

使用 Azure SQL 数据库或 Azure SQL 托管实例的一个好处是不必管理托管数据库的服务器。 但是，这个托管的基础结构也意味着必须重新考虑数据库优化。 你将不再致力于改进数据库硬件或网络基础结构。 Microsoft Azure 将控制这些环境。 你可以控制的主要方面是应用程序如何与 Azure SQL 数据库和 Azure SQL 托管实例交互。 批处理就是这些优化措施之一。

本文的第一部分比较了使用 Azure SQL 数据库或 Azure SQL 托管实例的 .NET 应用程序可用的各种批处理方法。 最后两个部分介绍批处理准则和方案。

批处理策略

请注意本文中的执行时间结果

事务

通过讨论事务来开始讲述批处理似乎有点奇怪。 但是使用客户端事务具有提高性能的微妙服务器端批处理效果。 可以使用几行代码来添加事务，因此这提供了一个快速提高顺序操作的性能的方法。

请注意以下 C# 代码，其中包含对一个简单表执行的插入和更新操作序列。

List<string> dbOperations = new List<string>();
dbOperations.Add("update MyTable set mytext = 'updated text' where id = 1");
dbOperations.Add("update MyTable set mytext = 'updated text' where id = 2");
dbOperations.Add("update MyTable set mytext = 'updated text' where id = 3");
dbOperations.Add("insert MyTable values ('new value',1)");
dbOperations.Add("insert MyTable values ('new value',2)");
dbOperations.Add("insert MyTable values ('new value',3)");

以下 ADO.NET 代码可按顺序执行这些操作。

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    conn.Open();

    foreach(string commandString in dbOperations)
    {
        SqlCommand cmd = new SqlCommand(commandString, conn);
        cmd.ExecuteNonQuery();
    }
}

优化此代码的最佳方法是实现这些调用的某种客户端批处理。 但是，只需包装事务中的调用序列，即可提高此代码的性能。 这是使用事务的相同代码。

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    conn.Open();
    SqlTransaction transaction = conn.BeginTransaction();

    foreach (string commandString in dbOperations)
    {
        SqlCommand cmd = new SqlCommand(commandString, conn, transaction);
        cmd.ExecuteNonQuery();
    }

    transaction.Commit();
}

事务实际在这两个示例中都用到了。 在第一个示例中，每个单个调用就是一个隐式事务。 在第二个示例中，用一个显式事务包装了所有调用。 按照预写事务日志的文档中所述，在事务提交时将日志记录刷新到磁盘。 因此通过在事务中包含更多调用，写入事务日志可能延迟，直到提交事务。 实际上，您正在为写入服务器事务日志启用批处理。

下表显示了一些即席测试结果。 这些测试执行具有事务和不具有事务的相同的顺序插入。 为了更具对比性，第一组测试是从笔记本电脑针对 Microsoft Azure 中的数据库远程运行的。 第二组测试是从位于同一 Microsoft Azure 数据中心（美国西部）内的云服务和数据库运行的。 下表显示具有和不具有事务的一系列顺序插入所用的时间（毫秒）。

本地到 Azure：

Azure 到 Azure（同一数据中心） ：

根据前面的测试结果，在事务中包装一个操作实际上会降低性能。 但是，当增加单个事务中的操作数时，性能提高将变得很明显。 当所有操作发生在 Microsoft Azure 数据中心内时，性能差异也更明显。 从 Microsoft Azure 数据中心外部使用 Azure SQL 数据库或 Azure SQL 托管实例所增加的延迟超过了使用事务带来的性能提升。

尽管使用事务可以提高性能，但还请继续遵循事务和连接的最佳做法。 使事务尽可能短，并在工作完成后关闭数据库连接。 前一个示例中的 using 语句可确保在后续代码阻塞完成时关闭连接。

前一个示例演示你可以将一个本地事务添加到任何具有两行的 ADO.NET 代码。 事务提供了一个快速提高代码性能的方法，这些代码用于执行顺序插入、更新和删除操作。 但是，为了实现最佳性能，请考虑进一步更改代码，以利用客户端批处理（如表值参数）。

有关 ADO.NET 中事务的详细信息，请参阅 ADO.NET 中的本地事务。

表值参数

表值参数支持用户定义的表类型作为 Transact-SQL 语句、存储过程和函数的参数。 使用这个客户端批处理方法，可以在表值参数中发送多行数据。 若要使用表值参数，请首先定义表类型。 以下 Transact-SQL 语句创建名为 MyTableType 的表类型。

    CREATE TYPE MyTableType AS TABLE
    ( mytext TEXT,
      num INT );

在代码中，你将创建一个名称和类型与表类型完全相同的DataTable。 在文本查询或存储过程调用中传递此参数 DataTable 。 以下示例显示了这个方法：

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    connection.Open();

    DataTable table = new DataTable();
    // Add columns and rows. The following is a simple example.
    table.Columns.Add("mytext", typeof(string));
    table.Columns.Add("num", typeof(int));
    for (var i = 0; i < 10; i++)
    {
        table.Rows.Add(DateTime.Now.ToString(), DateTime.Now.Millisecond);
    }

    SqlCommand cmd = new SqlCommand(
        "INSERT INTO MyTable(mytext, num) SELECT mytext, num FROM @TestTvp",
        connection);

    cmd.Parameters.Add(
        new SqlParameter()
        {
            ParameterName = "@TestTvp",
            SqlDbType = SqlDbType.Structured,
            TypeName = "MyTableType",
            Value = table,
        });

    cmd.ExecuteNonQuery();
}

在前面的示例中，对象 SqlCommand 插入表值参数 @TestTvp中的行。 以前创建 DataTable 的对象通过 SqlCommand.Parameters.Add 该方法分配给此参数。 对一个调用中的插入进行批处理将显著提高顺序插入的性能。

若要进一步改进前一个示例，请使用存储过程来替代基于文本的命令。 以下 Transact-SQL 命令创建采用表值参数的 SimpleTestTableType 存储过程。

CREATE PROCEDURE [dbo].[sp_InsertRows]
@TestTvp as MyTableType READONLY
AS
BEGIN
INSERT INTO MyTable(mytext, num)
SELECT mytext, num FROM @TestTvp
END
GO

然后将上一个代码示例中的对象声明更改为 SqlCommand 以下内容。

SqlCommand cmd = new SqlCommand("sp_InsertRows", connection);
cmd.CommandType = CommandType.StoredProcedure;

在大多数情况下，表值参数具有与其他批处理方法等效或更高的性能。 表值参数通常更可取，因为它们比其他选项更灵活。 例如，其他方法（如 SQL 大容量复制）仅允许插入新行。 但是使用表值参数，可以在存储过程中使用逻辑来决定更新哪些行和插入哪些行。 还可以修改表类型来包含“操作”列，该列指示指定的行应插入、更新还是删除。

下表显示使用表值参数的即席测试结果（毫秒）。

批处理带来的性能提升非常明显。 在上一次顺序测试中，1,000次操作在数据中心外部花费了129秒，在数据中心内部花费了21秒。 但是，使用表值参数时，1,000 个操作在数据中心外只需 2.6 秒，在数据中心内只需 0.4 秒。

有关表值参数的详细信息，请参阅表值参数。

SQL 批量复制

SQL 批量复制是另一种向目标数据库中插入大量数据的方法。 .NET 应用程序可以使用 SqlBulkCopy 类执行批量插入操作。 SqlBulkCopy 与命令行工具（ Bcp.exe或 Transact-SQL 语句 BULK INSERT）的函数类似。 下面的代码示例演示如何将源 DataTable表、表中的行批量复制到目标表 MyTable。

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    connection.Open();

    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "MyTable";
        bulkCopy.ColumnMappings.Add("mytext", "mytext");
        bulkCopy.ColumnMappings.Add("num", "num");
        bulkCopy.WriteToServer(table);
    }
}

在某些情况下，批量复制的效果好于表值参数。 请参阅文章BULK INSERT中 Table-Valued 参数与操作的比较表。

以下即席测试结果显示批处理的性能（以 SqlBulkCopy 毫秒为单位）。

在较小的批大小中，使用表值参数优于 SqlBulkCopy 类。 然而，对于 1,000 行和 10,000 行的测试，SqlBulkCopy 的执行速度比表值参数快 12-31%。 与表值参数一样，SqlBulkCopy 是批量插入的一个不错选择，尤其是与非批处理操作的性能相比。

有关 ADO.NET 中大容量复制的详细信息，请参阅 大容量复制操作。

多行参数化 INSERT 语句

对于小的批处理，一个替代方法是构造插入多个行的大型参数化 INSERT 语句。 以下代码示例演示了这个方法。

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    connection.Open();

    string insertCommand = "INSERT INTO [MyTable] ( mytext, num ) " +
        "VALUES (@p1, @p2), (@p3, @p4), (@p5, @p6), (@p7, @p8), (@p9, @p10)";

    SqlCommand cmd = new SqlCommand(insertCommand, connection);

    for (int i = 1; i <= 10; i += 2)
    {
        cmd.Parameters.Add(new SqlParameter("@p" + i.ToString(), "test"));
        cmd.Parameters.Add(new SqlParameter("@p" + (i+1).ToString(), i));
    }

    cmd.ExecuteNonQuery();
}

此示例用于演示基本概念。 一个更现实的方案是对所需的实体执行循环，以同时构造查询字符串和命令参数。 总共只能有 2,100 个查询参数，因此这会限制可以以这种方式处理的行总数。

以下即席测试结果显示此类插入语句的性能（毫秒）。

对于行数少于100的批次，此方法可能会更快一些。 尽管提高不大，但是此方法仍是一个可选方案，它可能在特定的应用程序方案下工作得很好。

DataAdapter

类 DataAdapter 允许修改 DataSet 对象，然后将更改提交为 INSERT、UPDATE 和 DELETE 操作。 如果以这种方式使用 DataAdapter，请务必注意，每个不同的操作都会进行单独的调用。 若要提高性能，请使用 UpdateBatchSize 属性来表示应一次批处理的作数。 有关详细信息，请参阅使用 DataAdapter 执行批处理操作。

实体框架

Entity Framework Core 支持批处理。

XML

为了完整性，我们认为讨论 XML 作为批处理策略非常重要。 但是，使用 XML 与其他方法相比没有什么优势，而且还有几个缺点。 此方法类似于表值参数，但是 XML 文件或字符串将传递到存储过程而非用户定义的表。 存储过程分析该存储过程中的命令。

此方法有几个缺点：

• 使用 XML 可能很繁琐，而且容易出错。
• 在数据库上分析 XML 可能占用大量 CPU。
• 在大多数情况下，使用此方法比使用表值参数慢。

出于这些原因，不建议对批处理查询使用 XML。

批处理注意事项

以下部分提供有关在 Azure SQL 数据库和 Azure SQL 托管实例应用程序中使用批处理的更多指南。

权衡

根据你的体系结构，批处理可能涉及性能和弹性之间的权衡。 例如，请考虑角色意外停止的情况。 如果丢失一行数据，其影响小于丢失一大批未提交的行的影响。 在指定时间窗口内将行发送到数据库之前对其进行缓冲会带来更大的风险。

因为要进行权衡，因此需要评估进行批处理的操作类型。 对相对不重要的数据进行更激进的批处理（即更大的批次，涉及更长的时间窗口）。

批大小

在我们的测试中，将大的批次拆分为更小的块通常没有好处。 实际上，这种拆分通常导致比提交单个大批次还要慢的性能。 例如，假设要插入 1,000 行。 下表显示了在拆分为较小的批处理时，使用表值参数插入 1,000 行所需的时间。

可以看到，对于 1,000 行，达到最佳性能的方法是一次性提交所有这些行。 在其他测试中（未在此处显示），将 10000 行拆分为两个包含 5000 行的批可略微提高性能。 但是这些测试的表架构相对简单，因此你应对自己的特定数据和批大小执行测试，以验证这些结果。

要考虑的另一个因素是如果总批大小变得太大，Azure SQL 数据库或 Azure SQL 托管实例可能限制并拒绝提交该批。 为了获得最佳结果，请测试特定方案，以确定是否有理想的批大小。 使批大小在运行时是可配置的，以允许基于性能或错误进行快速调整。

最后，平衡批大小和与批处理有关的风险。 如果出现暂时性错误或角色失败，请考虑重试操作或丢失批中数据的后果。

并行处理

如果采用减小批大小但是使用多个线程的方式来执行工作，会怎么样？ 我们的测试再次显示几个较小的多线程批次的性能通常比单个较大的批次性能差。 以下测试尝试在一个或多个并行批处理中插入 1,000 行。 此测试显示多个同时执行的批次实际上降低了性能。

并行度导致性能下降可能有以下几个原因：

• 有多个同时执行的网络调用而非一个。
• 针对一个表的多个操作可能导致争用和阻塞。
• 存在与多线程关联的开销。
• 打开多个连接的开销超过了并行处理带来的好处。

如果面向不同的表或数据库，则可以通过此策略获得一些性能提升。 在数据库分片或联合方案下，可能使用此方法。 分片使用多个数据库并将不同数据路由到每个数据库。 如果每个小批次针对不同数据库，则并行执行操作可能更有效。 但是，性能提升不足以用作决定在解决方案中使用数据库分片的基础。

在一些设计中，并行执行较小的批次可能导致将提高吞储量的请求置于负荷不大的系统中。 在这种情况下，即使处理单个较大的批处理更快，并行处理多个批处理可能更高效。

如果使用并行执行，请考虑控制最大工作线程数。 较小的数可能导致争用减少并且执行时间缩短。 此外，请注意这将增加目标数据库的连接和事务负载。

相关性能因素

有关数据库性能的通常准则也影响批处理。 例如，对于具有大的主键或很多非聚集索引的表，插入性能会下降。

如果表值参数使用存储过程，则可以使用过程开头的命令 SET NOCOUNT ON 。 此语句禁止返回过程中受影响的行的计数。 但是，在我们的测试中，使用 SET NOCOUNT ON 要么不起作用，要么性能下降。 使用表值参数中的单个 INSERT 命令，测试存储过程很简单。 更复杂的存储过程可能受益于此语句。 但不要假设在存储过程中添加SET NOCOUNT ON会自动提高性能。 若要了解效果，请使用和不使用 SET NOCOUNT ON 语句测试存储过程。

批处理方案

以下部分说明如何在三种应用程序方案下使用表值参数。 第一种方案显示缓冲和批处理如何可以配合工作。 第二种方案通过在单个存储过程调用中执行主-从操作来提高性能。 最后一种方案显示如何在“UPSERT”操作中使用表值参数。

缓冲

尽管一些方案中使用批处理很合适，但是也有很多方案下可以通过延迟处理来利用批处理。 但是，在出现意外故障时，延迟处理会使数据丢失的风险加大。 了解此风险并考虑后果非常重要。

例如，假设有一个跟踪每个用户的导航历史记录的 Web 应用程序。 对于每个页请求，该应用程序将创建一个数据库调用来记录用户的页面浏览量。 但是可以通过缓冲用户的导航活动，然后成批将此数据发送到数据库来提高性能和缩放性。 可用经历的时间和/或缓冲区大小来触发数据库更新。 例如，一个规则可能指定在 20 秒后或缓冲区达到 1000 项时应处理批。

以下代码示例使用反应扩展 - Rx 来处理监视类引发的缓冲事件。 当缓冲区已满或达到超时值时，使用表值参数将该批用户数据发送到数据库。

以下 NavHistoryData 类对用户导航详细信息进行建模。 它包含基本信息，如用户标识符、访问的 URL 和访问时间。

public class NavHistoryData
{
    public NavHistoryData(int userId, string url, DateTime accessTime)
    { UserId = userId; URL = url; AccessTime = accessTime; }
    public int UserId { get; set; }
    public string URL { get; set; }
    public DateTime AccessTime { get; set; }
}

该 NavHistoryDataMonitor 类负责将用户导航数据缓冲到数据库。 它包含一个方法， RecordUserNavigationEntry该方法通过引发事件 "OnAdded" 来响应。 以下代码显示一个构造函数逻辑，它使用 Rx 基于该事件来创建可查看的集合。 然后它使用 Buffer 方法订阅这个可观察集合。 重载指定应每隔 20 秒或 1,000 个条目发送一次缓冲区。

public NavHistoryDataMonitor()
{
    var observableData =
        Observable.FromEventPattern<NavHistoryDataEventArgs>(this, "OnAdded");

    observableData.Buffer(TimeSpan.FromSeconds(20), 1000).Subscribe(Handler);
}

处理程序将所有缓冲的项转换为表值类型，然后将此类型传递到处理该批的存储过程。 以下代码显示了这两个 NavHistoryDataEventArgs 类和 NavHistoryDataMonitor 类的完整定义。

public class NavHistoryDataEventArgs : System.EventArgs
{
    public NavHistoryDataEventArgs(NavHistoryData data) { Data = data; }
    public NavHistoryData Data { get; set; }
}

public class NavHistoryDataMonitor
{
    public event EventHandler<NavHistoryDataEventArgs> OnAdded;

    public NavHistoryDataMonitor()
    {
        var observableData =
            Observable.FromEventPattern<NavHistoryDataEventArgs>(this, "OnAdded");

        observableData.Buffer(TimeSpan.FromSeconds(20), 1000).Subscribe(Handler);
    }

处理程序将所有缓冲的项转换为表值类型，然后将此类型传递到处理该批的存储过程。 以下代码显示了这两个 NavHistoryDataEventArgs 类和 NavHistoryDataMonitor 类的完整定义。

    public class NavHistoryDataEventArgs : System.EventArgs
    {
        if (OnAdded != null)
            OnAdded(this, new NavHistoryDataEventArgs(data));
    }

    protected void Handler(IList<EventPattern<NavHistoryDataEventArgs>> items)
    {
        DataTable navHistoryBatch = new DataTable("NavigationHistoryBatch");
        navHistoryBatch.Columns.Add("UserId", typeof(int));
        navHistoryBatch.Columns.Add("URL", typeof(string));
        navHistoryBatch.Columns.Add("AccessTime", typeof(DateTime));
        foreach (EventPattern<NavHistoryDataEventArgs> item in items)
        {
            NavHistoryData data = item.EventArgs.Data;
            navHistoryBatch.Rows.Add(data.UserId, data.___URL, data.AccessTime);
        }

        using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
        {
            connection.Open();

            SqlCommand cmd = new SqlCommand("sp_RecordUserNavigation", connection);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(
                new SqlParameter()
                {
                    ParameterName = "@NavHistoryBatch",
                    SqlDbType = SqlDbType.Structured,
                    TypeName = "NavigationHistoryTableType",
                    Value = navHistoryBatch,
                });

            cmd.ExecuteNonQuery();
        }
    }
}

若要使用此缓冲类，应用程序将创建一个静态 NavHistoryDataMonitor 对象。 每次用户访问页面时，应用程序都会调用 。NavHistoryDataMonitor RecordUserNavigationEntry 方法。 缓冲逻辑继续执行，以将这些项成批发送到数据库。

主从

表值参数对于简单 INSERT 方案非常有用。 但是，它对于涉及多个表的成批插入作用不大。 “主/从”方案是一个很好的示例。 主表标识主实体。 一个或多个从表存储有关实体的更多数据。 在此方案中，外键关系将详细信息的关系强制实施到唯一的主实体。 请考虑简化版本的 PurchaseOrder 表及其关联的 OrderDetail 表。 以下 Transact-SQL 创建 PurchaseOrder 包含四列的表： OrderID、 OrderDate、 CustomerID和 Status。

CREATE TABLE [dbo].[PurchaseOrder](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [datetime] NOT NULL,
[CustomerID] [int] NOT NULL,
[Status] [nvarchar](50) NOT NULL,
CONSTRAINT [PrimaryKey_PurchaseOrder]
PRIMARY KEY CLUSTERED ( [OrderID] ASC ));

每个订单包含一个或多个产品采购。 该信息已记录在PurchaseOrderDetail表中。 以下 Transact-SQL 创建PurchaseOrderDetail包含五列的表：OrderID、OrderDetailID、ProductID、UnitPrice和OrderQty。

CREATE TABLE [dbo].[PurchaseOrderDetail](
[OrderID] [int] NOT NULL,
[OrderDetailID] [int] IDENTITY(1,1) NOT NULL,
[ProductID] [int] NOT NULL,
[UnitPrice] [money] NULL,
[OrderQty] [smallint] NULL,
CONSTRAINT [PrimaryKey_PurchaseOrderDetail] PRIMARY KEY CLUSTERED
( [OrderID] ASC, [OrderDetailID] ASC ));

OrderID表中的PurchaseOrderDetail列必须引用PurchaseOrder表中的订单。 外键的以下定义实施此约束。

ALTER TABLE [dbo].[PurchaseOrderDetail]  WITH CHECK ADD
CONSTRAINT [FK_OrderID_PurchaseOrder] FOREIGN KEY([OrderID])
REFERENCES [dbo].[PurchaseOrder] ([OrderID]);

为了使用表值参数，你对每个目标表必须具有一个用户定义的表类型。

CREATE TYPE PurchaseOrderTableType AS TABLE
(   OrderID INT,
    OrderDate DATETIME,
    CustomerID INT,
    Status NVARCHAR(50) );
GO

CREATE TYPE PurchaseOrderDetailTableType AS TABLE
(   OrderID INT,
    ProductID INT,
    UnitPrice MONEY,
    OrderQty SMALLINT );
GO

然后定义接受这些类型的表的存储过程。 此过程允许应用程序对单个调用中的一组订单和订单详细信息在本地进行批处理。 以下 Transact-SQL 提供此采购订单示例的完整存储过程声明。

CREATE PROCEDURE sp_InsertOrdersBatch (
@orders as PurchaseOrderTableType READONLY,
@details as PurchaseOrderDetailTableType READONLY )
AS
SET NOCOUNT ON;

-- Table that connects the order identifiers in the @orders
-- table with the actual order identifiers in the PurchaseOrder table
DECLARE @IdentityLink AS TABLE (
SubmittedKey int,
ActualKey int,
RowNumber int identity(1,1)
);

-- Add new orders to the PurchaseOrder table, storing the actual
-- order identifiers in the @IdentityLink table
INSERT INTO PurchaseOrder ([OrderDate], [CustomerID], [Status])
OUTPUT inserted.OrderID INTO @IdentityLink (ActualKey)
SELECT [OrderDate], [CustomerID], [Status] FROM @orders ORDER BY OrderID;

-- Match the passed-in order identifiers with the actual identifiers
-- and complete the @IdentityLink table for use with inserting the details
WITH OrderedRows As (
SELECT OrderID, ROW_NUMBER () OVER (ORDER BY OrderID) As RowNumber
FROM @orders
)
UPDATE @IdentityLink SET SubmittedKey = M.OrderID
FROM @IdentityLink L JOIN OrderedRows M ON L.RowNumber = M.RowNumber;

-- Insert the order details into the PurchaseOrderDetail table,
-- using the actual order identifiers of the master table, PurchaseOrder
INSERT INTO PurchaseOrderDetail (
[OrderID],
[ProductID],
[UnitPrice],
[OrderQty] )
SELECT L.ActualKey, D.ProductID, D.UnitPrice, D.OrderQty
FROM @details D
JOIN @IdentityLink L ON L.SubmittedKey = D.OrderID;
GO

在此示例中，本地定义的 @IdentityLink 表存储新插入行的实际 OrderID 值。 这些顺序标识符与表值参数中的OrderID@orders临时@details值不同。 因此，@IdentityLink 表将 OrderID 参数中的 @orders 值与 OrderID 表中新行的实际 PurchaseOrder 值连接起来。 执行此步骤后， @IdentityLink 表可以方便地插入满足外键约束的实际 OrderID 值的订单详细信息。

可以从代码或其他 Transact-SQL 调用使用此存储过程。 有关代码示例，请参阅本文的表值参数部分。 以下 Transact-SQL 演示如何调用 sp_InsertOrdersBatch。

declare @orders as PurchaseOrderTableType;
declare @details as PurchaseOrderDetailTableType;

INSERT @orders
([OrderID], [OrderDate], [CustomerID], [Status])
VALUES(1, '1/1/2013', 1125, 'Complete'),
(2, '1/13/2013', 348, 'Processing'),
(3, '1/12/2013', 2504, 'Shipped');

INSERT @details
([OrderID], [ProductID], [UnitPrice], [OrderQty])
VALUES(1, 10, $11.50, 1),
(1, 12, $1.58, 1),
(2, 23, $2.57, 2),
(3, 4, $10.00, 1);

EXEC sp_InsertOrdersBatch @orders, @details;

此解决方案允许每个批处理使用一组 OrderID 从 1 开始的值。 这些临时 OrderID 值描述批处理中的关系情况，但在插入操作时确定实际 OrderID 值。 可以重复运行前一示例中的相同语句，在数据库中生成唯一订单。 为此，请考虑在使用此批处理方法时添加防止重复订单的更多代码或数据库逻辑。

此示例演示使用表值参数可以成批执行更复杂的数据库操作（如主-从操作）。

UPSERT

另一批处理方案涉及同时更新现有行和插入新行。 此操作有时称为“UPSERT”（更新 + 插入）操作。 一个合适的替代方法是使用 MERGE 语句，而不是单独调用 INSERT 和 UPDATE。 MERGE 语句可以在单个调用中执行插入和更新操作。 MERGE 语句锁定机制的工作原理与单独的 INSERT 和 UPDATE 语句的不同。 在部署到生产环境之前测试特定工作负载。

可以将表值参数用于 MERGE 语句以执行更新和插入。 例如，请考虑使用包含以下列的简化 Employee 表：EmployeeID、FirstName、LastName、SocialSecurityNumber：

CREATE TABLE [dbo].[Employee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[SocialSecurityNumber] [nvarchar](50) NOT NULL,
CONSTRAINT [PrimaryKey_Employee] PRIMARY KEY CLUSTERED
([EmployeeID] ASC ))

在此示例中，可以使用 SocialSecurityNumber 是唯一的这个事实来执行多个员工的 MERGE。 首先，创建用户定义的表类型：

CREATE TYPE EmployeeTableType AS TABLE
( Employee_ID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    SocialSecurityNumber NVARCHAR(50) );
GO

接下来，创建一个使用 MERGE 语句的存储过程或编写包含该语句的代码来执行更新和插入。 以下示例使用针对类型为 EmployeeTableType 的表值参数 @employees 的 MERGE 语句。 @employees 表的内容在此处未显示。

MERGE Employee AS target
USING (SELECT [FirstName], [LastName], [SocialSecurityNumber] FROM @employees)
AS source ([FirstName], [LastName], [SocialSecurityNumber])
ON (target.[SocialSecurityNumber] = source.[SocialSecurityNumber])
WHEN MATCHED THEN
UPDATE SET
target.FirstName = source.FirstName,
target.LastName = source.LastName
WHEN NOT MATCHED THEN
    INSERT ([FirstName], [LastName], [SocialSecurityNumber])
    VALUES (source.[FirstName], source.[LastName], source.[SocialSecurityNumber]);

有关详细信息，请参阅 MERGE 语句的文档和示例。 尽管可以在包含单独 INSERT 和 UPDATE 操作的多步骤存储过程调用中完成同样的工作，但是 MERGE 语句更有效。 数据库代码还可以构造直接使用 MERGE 语句的 Transact-SQL 调用而无需对 INSERT 和 UPDATE 使用两个数据库调用。

建议摘要

以下列表提供了本文中讨论的批处理建议的摘要：

• 使用缓冲和批处理可提高 Azure SQL 数据库和 Azure SQL 托管实例应用程序的性能和可缩放性。
• 了解批处理/缓冲和弹性之间的权衡问题。 在角色失败期间，可能遗失一批尚未处理的商务关键数据，这种风险超过批处理带来的性能优点。
• 尝试将所有数据库调用纳入单一数据中心以缩短延迟。
• 如果选择单个批处理方法，使用表值参数可实现最佳性能和灵活性。
• 要实现最快速的插入性能，请遵循以下常规准则，但是要针对方案进行测试：
  • 对于 < 100 行，请使用单个参数化 INSERT 命令。
  • 对于 < 1,000 行，请使用表值参数。
  • 对于 >= 1000 行，请使用 SqlBulkCopy。
• 对于更新和删除操作，请将表值参数用于存储过程逻辑，该逻辑确定对表参数中每行的正确操作。
• 批大小准则：
  • 使用可满足应用程序和业务需求的最大批大小。
  • 掌握好大批次带来的性能提升与临时或灾难性故障的风险之间的平衡。 批中数据重试或丢失的后果是什么？
  • 测试最大的批大小，以验证 Azure SQL 数据库或 Azure SQL 托管实例是否未拒绝它。
  • 创建控制批处理的配置设置，如批大小或缓冲时间窗口。 这些设置提供灵活性。 可以在生产中更改批处理行为，而无需重新部署云服务。
• 避免并行执行对一个数据库中单个表进行操作的批处理。 如果选择将单个批分配给多个工作线程，请运行测试来确定理想的线程数。 在达到某个阈值后，线程增加将导致性能下降而非提升。
• 请考虑对大小和时间进行缓冲，为更多方案实现批处理。

相关内容

• 在 Azure SQL 数据库中优化应用程序和数据库以提高性能
• 弹性池有助于在 Azure SQL 数据库中管理和缩放多个数据库