如何使用批次處理來改善 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 大量複製，只允許插入新的資料列。 但是，使用資料表值參數，您可以使用預存程序中的邏輯來判斷哪些資料列是更新和插入的資料列。 資料表類型也可以修改為包含「作業」資料行，指出是否應該插入、更新或刪除指定的資料列。

下表顯示了使用資料表值參數的隨機測試結果 (以毫秒為單位)。

批次處理的效能提升會立即顯現出來。 在先前的循序測試中，1000 個作業在資料中心外花費了 129 秒，在資料中心內需要 21 秒。 但使用資料表值參數時，1000 個作業在資料中心外只需 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 作業的比較資料表。

下列隨機測試結果顯示使用 SqlBulkCopy 進行批次處理的效能 (以毫秒為單位)。

在較小的批次大小中，使用資料表值參數的效能優於 SqlBulkCopy 類別。 不過，SqlBulkCopy 針對 1,000 及 10,000 個資料列的測試執行速度比資料表值參數快 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();
}

此範例旨在展示基本概念。 更真實的案例會迴圈執行必要的實體，以同時建構查詢字串與命令參數。 您最多只能使用 2100 個查詢參數，因此這會限制可以此方式處理的資料列總數。

下列隨機測試結果顯示此類 INSERT 陳述式的效能 (以毫秒為單位)。

對於小於 100 個資料列的批次處理，此方法可能會稍微快一點。 雖然改進很小，但此技術是另一種可能在您的特定應用案例發揮作用的選項。

DataAdapter

DataAdapter 類別可讓您修改 DataSet 物件，然後將變更作為 INSERT、UPDATE 及 DELETE 作業提交。 如果您以這種方式使用 DataAdapter ，請務必注意，會針對每個不同的作業進行個別呼叫。 若要改善效能，請使用 UpdateBatchSize 屬性，將一次批次處理的作業數目。 如需詳細資訊，請參閱使用 DataAdapter 執行批次處理作業。

Entity Framework

Entity Framework Core 支援批次處理。

XML

為了完整起見，我們認為將 XML 作為批次處理策略進行討論很重要。 然而，與其他方法相比，使用 XML 沒有任何優點，且有一些缺點。 此方法類似於資料表值參數，但 XML 檔案或字串會傳遞至預存程序，而不是使用者定義的資料表。 預存程序會剖析預存程式中的命令。

此方法有幾個缺點：

• 使用 XML 可能會很麻煩且容易出錯。
• 剖析資料庫上的 XML 可能需要大量 CPU。
• 在大部分情況下，此方法比資料表值參數慢。

基於這些理由，不建議對批次查詢使用 XML。

批次處理注意事項

下列各節提供在 Azure SQL 資料庫及 Azure SQL 受控執行個體應用程式中使用批次處理的更多指南。

取捨

根據您的結構，批次處理可能會牽涉到效能與復原之間的取捨。 例如，請考慮您的角色意外關閉的案例。 如果您遺失一個資料列，其影響會小於遺失大量未提交資料列的影響。 當您在指定的時間範圍將資料列傳送至資料庫之前緩衝資料列時，會有更大的風險。

由於此取捨，請評估您批次處理的操作類型。 對不太重要的資料進行更積極的批次處理 (較大的批次處理及更長的時間範圍) 。

批次大小

在我們的測試中，將大批次處理分成較小的區塊通常沒有任何優點。 事實上，與提交單一大批次相比，這種細分通常會導致效能下降。 例如，假設您想要插入 1000 個資料列。 下表顯示當分割成較小的批次時，使用資料表值參數插入 1000 個資料列所需的時間。

您可以看到 1000 個資料列的最佳效能是一次提交全部資料列。 在其他測試中 (此處未顯示)，將 10000 個資料列批次分成 5000 個批次，效能會略有提升。 但是這些測試的資料表結構相對簡單，因此您應該對特定的資料和批次大小執行測試，以驗證這些結果。

另一個需要考慮的因素是，如果批次總數變得太大，Azure SQL 資料庫或 Azure SQL 受控執行個體可能會節流並拒絕提交批次。 為了獲得最佳結果，請測試您的特定案例，以確定是否有理想的批次大小。 使批次大小在運行時可進行設定，以便根據效能或錯誤進行快速調整。

最後，將批次的大小與批次處理相關的風險進行平衡。 如果發生暫時性錯誤或角色失敗，請考慮重試作業或批次中遺失資料的後果。

平行處理

如果您採取了減少批次大小的方法，但使用多個執行緒來執行工作會怎麼樣？ 我們的測試再次表明，數個較小的多執行緒批次通常會比單一較大的批次表現更差。 下列測試會嘗試在一或多個平行批次中插入 1000 個資料列。 此測試顯示了更多的同時批次處理實際上如何降低效能。

由於平行處理原則，效能降低有數個可能的原因：

• 有多個同時的網路呼叫，而不是一個。
• 單一資料表的多個作業可能會導致爭用和封鎖。
• 有與多執行緒相關的額外負荷。
• 開啟多個連線的費用超過平行處理的優點。

如果您以不同的資料表或資料庫為目標，則可能會看到此策略的一些效能提升。 資料庫分區化或同盟是此方法的案例。 分區化會使用多個資料庫，並將不同的資料路由傳送至每個資料庫。 如果每個小型批次處理都前往不同的資料庫，則平行執行作業會更有效率。 不過，效能提升不足以作為決定在解決方案中使用資料庫分區化的基礎。

在某些設計中，較小批次的平行執行可以提高負載下系統中要求的輸送量。 在此情況下，儘管處理單一較大批次的速度更快，但平行處理多個批次可能更有效率。

如果您使用平行執行，請考慮控制背景工作執行緒的最大數量。 較小的數字可能會減少爭用並加快執行時間。 此外，請考慮這在連線和異動中為目標資料庫帶來的額外負載。

相關的效能因素

資料庫效能的一般指南也會影響批次處理。 例如，對於具有大型主索引鍵或許多非叢集索引的資料表，插入效能會降低。

如果資料表值參數使用預存程序，您可以在程式開頭使用 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 秒或 1000 個項目傳送一次。

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 ))

PurchaseOrderDetail 資料表中的 OrderID 資料行必須引用 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 值。 這些訂單識別碼與 @orders 和 @details 資料表值參數中的暫存 OrderID 值不同。 因此，@IdentityLink 資料表接著會將 @orders 參數中的 OrderID 值連接到 PurchaseOrder 資料表中新資料列的實際 OrderID 值。 完成此步驟後， @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

此解決方案可讓每個批次使用一組從 1 開始的 OrderID 值。 這些暫存 OrderID 值描述批次中的關聯性，但在插入作業時會決定實際的 OrderID 值。 您可以在上述範例中重複執行相同的陳述式，並在資料庫中產生唯一的訂單。 因此，請考慮新增更多程式碼或資料庫邏輯，以防止使用此批處理技術時重複訂單。

此範例示範可以使用資料表值參數來批處理更複雜的資料庫作業，例如主從式作業。

更新插入

另一個批處理案例牽涉到同時更新現有的資料列和插入新資料列。 此作業有時稱為「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 陳述式更有效率。 資料庫程式碼也可以建構 Transact-SQL 呼叫，直接使用 MERGE 陳述式，而不需要對 INSERT 和 UPDATE 進行兩個資料庫呼叫。

建議摘要

下列清單提供本文所討論批次處理建議的摘要：

• 使用緩衝處理及批次處理來提升 Azure SQL 資料庫和 Azure SQL 受控執行個體應用程式的效能和擴充性。
• 瞭解批次處理/緩衝和復原之間的取捨。 在角色故障期間，遺失未處理的關鍵業務資料批次的風險可能超過批次處理的效能優勢。
• 嘗試在單一資料中心內保留對資料庫的所有呼叫，以減少延遲。
• 如果您選擇單一批次處理技術，資料表值參數可提供最佳的效能與彈性。
• 若要獲得最快的插入效能，請遵循下列一般指南，但測試您的案例：
  • 針對 < 100 個資料列，請使用單一參數化 INSERT 命令。
  • 針對 < 1000 個資料列，請使用資料表值參數。
  • 針對 >= 1000 個資料列，請使用 SqlBulkCopy。
• 針對更新及刪除作業，請使用資料表值參數搭配預存程序邏輯，以決定資料表參數中每個資料列的正確作業。
• 批次大小指南：
  • 使用對您應用程式及業務需求有意義的最大批次大小。
  • 平衡大型批次的效能提升與暫時性或災難性故障的風險。 重試或批次中資料遺失的後果為何？
  • 測試最大的批次大小，以確認 Azure SQL 資料庫或 Azure SQL 受控執行個體不會拒絕它。
  • 建立可控制批次處理的組態設定，例如批次大小或緩衝時間範圍。 這些設定提供彈性。 您可以在生產環境變更批次處理行為，而無需重新部署雲端服務。
• 避免在單一資料庫中的單一資料表上操作的批次平行執行。 如果您選擇將單一批次分割至多個背景工作執行緒，請執行測試以判斷理想的執行緒數目量。 在未指定的臨界值之後，更多的執行緒會降低效能，而不是提高效能。
• 請考慮緩衝處理大小及時間，以實作更多案例的批次處理方式。

下一步

本文著重於與批次處理相關的資料庫設計和程式碼撰寫技術如何改善應用程式效能與擴充性。 但這只是整體策略的一個因素。 如需提升效能和擴充性的詳細資訊，請參閱資料庫效能指南與彈性集區的價格及效能注意事項。