匯出 (0) 列印
全部展開

Azure 中 SQL Database 應用程式的批次技術

更新日期: 2015年1月

作者::Jason Roth、Silvano Coriani、Trent Swanson (Full Scale 180 Inc)

審稿者:Conor Cunningham、Michael Thomassy

將作業以批次方式交給 SQL 資料庫 處理可以顯著改善 Microsoft Azure 中雲端服務的效能和延展性。本文一開始先說明批次處理的效益,然後介紹各種技術、案例和考量,幫助您順利的在雲端服務中使用批次處理。

  1. 為什麼批次處理對 SQL Database 很重要?

  2. 批次處理策略

    1. 交易

    2. 資料表值參數

    3. SQL 大量複製

    4. 多資料列參數化 INSERT 陳述式

    5. DataAdapter

    6. Entity Framework

    7. XML

  3. 批次處理考量

    1. 取捨

    2. 批次大小

    3. 平行處理

    4. 相關效能因素

  4. 批次處理案例

    1. 緩衝

    2. 主檔/明細

    3. UPSERT

  5. 建議

以批次方式呼叫遠端服務是眾所周知能夠提升效能和延展性的策略。與遠端服務的任何互動 (例如序列化、網路傳輸和還原序列化) 都有固定的處理成本。如果將許多個別交易包裝成一個批次,就可以將這些成本降到最低。

本文會檢視各種 SQL 資料庫 批次處理策略和案例。雖然這些策略對使用 SQL Server 的內部部署應用程式也一樣重要,但是強調 SQL 資料庫 的批次處理使用方式有兩個主要原因:

  • 存取 SQL 資料庫 時網路延遲可能較長,尤其是從同一個 SQL 資料庫 資料中心外部存取 Microsoft Azure 時。

  • SQL 資料庫 的多租用戶特性表示資料存取層的效率與資料庫的整體延展性相關。SQL 資料庫 必須防止任何單一租用戶/使用者獨佔資料庫資源,而損及其他租用戶。SQL 資料庫 在回應過量使用預先定義的配額時,可以減少輸送量或是以節流例外回應。像批次處理這類效率可讓您在達到這些限制前於 SQL 資料庫 上完成更多工作。批次處理對使用多個資料庫或同盟的架構也很有效。您與每個資料庫單元的互動效率仍然是整體延展性的關鍵因素。

使用 SQL 資料庫 的一個優點是,您不必管理主控資料庫的伺服器。但是這種受到管理的基礎結構也表示您必須從不同角度思考資料庫最佳化。您再也不能設法改善資料庫硬體或網路基礎結構。這些環境都由 Microsoft Azure 控制。您可以控制的主要範圍是您的應用程式與 SQL 資料庫 的互動方式。批次處理就屬於這類最佳化。

本文第一篇檢視 .NET 應用程式使用 SQL 資料庫 的各種批次技術。最後兩節討論批次處理方針和案例。

檢視批次處理的分析卻從討論交易開始似乎有點奇怪。但是使用用戶端交易會對伺服器端批次處理產生微妙的影響,足以改善效能。而且只要少數幾行程式碼就可以加入交易,所以是一種快速改善循序作業效能的方法。

設想以下針對簡單資料表執行的一序列插入和更新作業。

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 資料中心 (West US) 的雲端服務和資料庫執行。下表顯示在有交易和沒有交易的情況下,循序插入的持續毫秒數。

 

作業 內部部署到 Microsoft Azure Microsoft Azure (相同資料中心)

無交易 (毫秒)

交易 (毫秒)

無交易 (毫秒)

交易 (毫秒)

1

130

402

21

26

10

1208

1226

220

56

100

12662

10395

2145

341

1000

128852

102917

21479

2756

note附註
結果並不是基準,目的只是要顯示相對效能。時間是依據至少 10 個測試回合的平均值。作業是插入空的資料表。

將單一作業包裝在交易中其實會降低效能。但是,隨著單一交易中的作業數目增加,效能改善就會變得比較明顯。所有作業都在 Microsoft Azure 資料中心內進行時,效能差異也比較明顯。從 SQL 資料庫 資料中心外部使用 Microsoft Azure 所增加的延遲會減低使用交易所提升的效能。

雖然使用交易可以提高效能,請繼續遵守交易和連接的最佳做法。交易盡量保持越短越好,而且在工作完成後要關閉資料庫連接。上例中的 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 大量複製等其他技術只允許插入新資料列。但是如果使用資料表值參數,就可以在預存程序中使用邏輯來判斷要更新哪些資料列、插入哪些資料列。資料表類型也可以修改成包含 "Operation" 資料行,以指示應該插入、更新或刪除指定的資料列。

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

 

作業 內部部署到 Microsoft Azure (毫秒) Microsoft Azure,相同資料中心 (毫秒)

1

124

32

10

131

25

100

338

51

1000

2615

382

10000

23830

3586

note附註
結果並不是基準,目的只是要顯示相對效能。這些測試使用類似本節範例的預存程序。時間是依據至少 10 個測試回合的平均值。作業是插入空的資料表。

因批次處理而提升的效能立即顯而易見。在上一個循序範例中,1000 次作業從資料中心外部花費 129 秒,在資料中心內花費 21 秒。但是若使用資料表值參數,1000 次作業從資料中心外部只需 2.6 秒,在資料中心內則只需 0.4 秒。

如需有關資料表值參數的詳細資訊,請參閱 資料表值參數 (Database Engine)

SQL 大量複製是另一種在目標資料庫中插入大量資料的方式。.NET 應用程式可以使用 SqlBulkCopy 類別執行大量插入作業。SqlBulkCopy 的功能類似命令列工具 Bcp.exe 或 Transact-SQL 陳述式 BULK INSERT。下列程式碼範例示範如何將來源 DataTabletable 中的資料列大量複製到 SQL Server 中的目的地資料表 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);
    }
}

在某些情況下,大量複製會比資料表值參數合適。請參閱 資料表值參數 (Database Engine) 主題中有關資料表值參數與 BULK INSERT 作業的比較表。

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

 

作業 內部部署到 Microsoft Azure (毫秒) Microsoft Azure,相同資料中心 (毫秒)

1

433

57

10

441

32

100

636

53

1000

2535

341

10000

21605

2737

note附註
結果並不是基準,目的只是要顯示相對效能。時間是依據至少 10 個測試回合的平均值。作業是插入空的資料表。

當批次大小較小時,使用資料表值參數的效能勝過 SqlBulkCopy 類別。但是在測試 1,000 和 10,000 個資料列時,SqlBulkCopy 的執行速度就比資料表值參數快了 12-31%。就像資料表值參數一樣,SqlBulkCopy 是批次插入的好選項,尤其是與非批次作業的效能做比較。

如需有關 ADO.NET 中大量複製的詳細資訊,請參閱 SQL Server 中的大量複製作業

小批次有一個替代選項,就是建構會插入多個資料列的大型參數化 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 (毫秒)

1

32

20

10

30

25

100

33

51

note附註
結果並不是基準。時間是依據至少 30 個測試回合的平均值。作業是插入空的資料表。

在少於 100 個資料列時,這種做法比批次方式略快。雖然改善有限,但是這種技術提供了另一種選擇,或許正適合您特定的應用程式案例。

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

Entity Framework 目前不支援批次處理。社群中有不同開發人員嘗試過各種因應措施,例如覆寫 SaveChanges 方法。但是這些解決方案通常很複雜,而且是針對應用程式和資料模型量身訂製的。Entity Framework codeplex 專案目前有一個針對這項功能要求的討論頁。若要檢視這些討論,請參閱 設計會議記錄 - 2012 年 8 月 2 日

我們認為必須討論將 XML 做為批次策略,討論才算完整。但是相對於其他方法,使用 XML 沒有任何優點,反而有幾個缺點。這種做法類似資料表值參數,只是傳給預存程序的是 XML 檔案或字串,而不是使用者定義的資料表。預存程序會剖析預存程序中的命令。

這種做法有幾個缺點:

  1. 處理 XML 可能很繁瑣,容易出錯。

  2. 針對資料庫剖析 XML 可能很耗費 CPU。

  3. 在大部分情況下,這種方法的速度比資料表值參數慢。

基於這些理由,所以不建議使用 XML 進行批次查詢。

下列各節提供在 SQL 資料庫 應用程式中使用批次處理的詳細指引。

根據您的架構,批次處理可能意味著得在效能和恢復能力之間做取捨。例如,假設發生角色異常中斷的狀況。如果遺失一個資料列的資料,所造成的影響會小於遺失大批尚未認可的資料列。如果將資料列傳送到資料庫之前,先將資料列放在緩衝區一段指定的時間間隔,那麼風險更大。

因為有這些權衡取捨,所以請評估您要批次處理的作業類型。比較不重要的資料可以用比較大膽的方式批次處理 (較大的批次和較長的時間間隔)。

在我們的測試中,通常將大批次分成小塊不會有任何好處。事實上,這樣切割所產生的效能往往比提交一個大批次更慢。例如,假設要插入 1000 個資料列。下表顯示使用資料表值參數插入 1000 個資料列、而且分成較小的批次時要花多少時間。

 

批次大小 [反覆] 資料表值參數 (毫秒)

1000 [1]

347

500 [2]

355

100 [10]

465

50 [20]

630

note附註
結果並不是基準。內部部署測試是透過無線網路從膝上型電腦執行。時間是依據至少 10 個測試回合的平均值。作業是插入空的資料表。

您可以看到同時提交 1000 個資料列的效能最好。在其他測試中 (這裡未顯示),將 10000 個資料列批次分成兩個 5000 列的批次會提升少許效能。但是這些測試的資料表結構描述相當簡單,所以您必須針對您特有的資料和批次大小執行測試,確認這些研究結果。

另一個要考慮的因素是,如果總批次變得太大,SQL 資料庫 可能會節流並拒絕認可批次。為獲得最好的結果,請測試您具體的案例,判斷是否有理想的批次大小。將批次大小設為可在執行階段設定,以根據效能或錯誤快速調整。

最後,請在批次大小與批次處理風險之間取得平衡。如果發生暫時性錯誤或角色失敗,請考慮重試作業或遺失批次資料的後果。

如果採用縮小批次大小的方法,但使用多個執行緒來執行工作呢?同樣地,我們的測試顯示,數個較小的多執行緒批次的執行效能通常不如一個較大的批次。下列測試嘗試以一個或多個平行批次插入 1000 個資料列。這個測試顯示較多個同時批次如何竟然降低效能。

 

批次大小 [反覆] 2 個執行緒 (毫秒) 4 個執行緒 (毫秒) 6 個執行緒 (毫秒)

1000 [1]

277

315

266

500 [2]

548

278

256

250 [4]

405

329

265

100 [10]

488

439

391

note附註
結果並不是基準。時間是依據至少 10 個測試回合的平均值。作業是插入空的資料表。平行處理測試使用 .NET 工作平行程式庫。

因為平行處理原則而降低效能的原因可能包括:

  1. 同時有多個網路呼叫,而不只一個。

  2. 針對單一資料表的多項作業可能造成競爭和封鎖。

  3. 多執行緒處理會產生額外負擔。

  4. 開啟多個連接的代價超過平行處理的效益。

如果目標是不同資料表或資料庫,有可能因為這種策略而提升一點效能。資料庫分區化或同盟就是這種做法的案例。分區化會使用多個資料庫,並將不同資料路由傳送至每個資料庫。如果每個小批次是前往不同資料庫,則平行執行這些作業就可能比較有效率。但是,提升的效能並不明顯,還不足以做為在方案中使用資料庫分區化的決策基礎。

在一些設計中,平行執行較小的批次可能會在欠載的系統上使要求輸送量獲得改善。在這種情況下,雖然處理一個大批次的速度比較快,但還是平行處理多個批次會比較有效率。

如果您使用平行執行,請考慮控制最大背景工作執行緒的數目。數目較小會減少競爭並加快執行時間。此外,還要考慮這種做法在連接和交易方面為目標資料庫帶來的額外負載。

有關資料庫效能的一般指引也會影響批次處理。比如說,如果資料表的主索引鍵很大或有許多非叢集索引,其插入效能就會降低。

如果資料表值參數使用預存程序,您可以在程序開始時使用 SET NOCOUNT ON 命令。這個陳述式會抑制傳回程序中受影響的資料列計數。但是在我們的測試中,使用 SET NOCOUNT ON 可能沒有任何作用或是會降低效能。測試預存程序很簡單,只有一個來自資料表值參數的 INSERT 命令。比較複雜的預存程序或許可能受益於這個陳述式。但是,請勿假設在預存程序中加入 SET NOCOUNT ON 就會自動改善效能。若要了解效果,請在有和沒有 SET NOCOUNT ON 陳述式的情況下測試您的預存程序。

下列各節描述如何在 3 個應用程式案例中使用資料表值參數。第一個案例示範緩衝和批次處理如何一起運作。第二個案例在單一預存程序呼叫中執行「主檔/明細」作業以改善效能。最後一個案例示範如何在 "UPSERT" 作業中使用資料表值參數。

雖然某些狀況並不是明顯應該使用批次處理的狀況,但還是有許多狀況可透過延遲處理而利用到批次的優點。然而,延遲處理也帶來更大的風險,因為資料可能在未預期的失敗情況下遺失。所以務必了解這種風險並考慮後果。

例如,假設有一個 Web 應用程式是用來追蹤每個使用者的導覽記錄。應用程式可以針對每一個頁面要求執行一個資料庫呼叫,記錄使用者的頁面檢視。但是,如果將使用者的導覽活動緩衝起來,然後再將這些資料整批傳給資料庫,就可以達到較高的效能和延展性。您可以利用經過時間和 (或) 緩衝區大小來觸發資料庫更新。例如,規則可以指定應該在 20 秒後或緩衝區達到 1000 個項目時處理批次。

下列程式碼範例使用 Reactive Extensions (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);           
}

處理常式會將所有緩衝項目轉換成資料表值類型,然後將這個類型傳遞給處理批次的預存程序。下列程式碼顯示 NavHistoryDataEventArgsNavHistoryDataMonitor 類別的完整定義。

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

    public void RecordUserNavigationEntry(NavHistoryData data)
    {    
        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 會建立有 4 個資料行的 PurchaseOrder 資料表:OrderIDOrderDateCustomerIDStatus

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 會建立有 5 個資料行的 PurchaseOrderDetail 資料表:OrderIDOrderDetailIDProductIDUnitPriceOrderQty

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].[PurchaseOrderDetail2]  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

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

這個範例說明即使比較複雜的資料庫作業 (例如主檔/明細作業) 也可以使用資料表值參數進行批次處理。

另一種批次案例涉及更新現有資料列和插入新資料列同時進行。這種作業有時稱為 "UPSERT" (更新 + 插入) 作業。這種作業最適合使用 MERGE 陳述式,而不是執行個別的 INSERT 和 UPDATE 呼叫。MERGE 陳述式可以在單一呼叫中同時執行插入和更新作業。

資料表值參數可以搭配 MERGE 陳述式一起使用,來執行更新和插入。 例如,假設有一個簡化的 Employee 資料表,包含下列資料行:EmployeeIDFirstNameLastNameSocialSecurityNumber:

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 陳述式執行更新和插入。下列範例會對 @employees 類型的資料表值參數 EmployeeTableType 使用 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 兩個資料庫呼叫。

  • 使用緩衝和批次處理可以提升 SQL 資料庫 應用程式的效能和延展性。

  • 了解批次處理/緩衝之間的權衡取捨及恢復功能。在角色失敗時,尚未處理之業務關鍵資料的遺失風險可能超過批次處理的效能效益。

  • 嘗試將所有資料庫呼叫保持在單一資料中心內,以降低延遲。

  • 如果選擇單一批次處理技術,資料表值參數 提供最佳的效能和彈性。

  • 為達到最快速的插入效能,請遵守這些一般方針,但是要測試您的案例:

  • 針對更新和刪除作業,使用資料表值參數搭配預存程序邏輯,判斷資料表參數中每個資料列的正確作業。

  • 指次大小方針:

    • 使用符合您的應用程式和業務需求的最大批次大小。

    • 在大型批次提升的效能與暫時性或災難性失敗的風險之間取得平衡。重試或遺失批次資料的後果是什麼?

    • 測試最大的批次大小,以驗證 SQL 資料庫 不會拒絕該批次。

    • 建立控制批次處理的組態設定,例如批次大小或緩衝時間間隔。這些設定會提供彈性。您可以在實際執行中變更批次處理行為,而不必重新部署雲端服務。

  • 避免平行執行對一個資料庫中單一資料表運作的批次。如果選擇將一個批次分成多個背景工作執行緒,要執行測試判斷理想的執行緒數目。在未指定的臨界值之後,更多執行緒只會降低效能而不是提高效能。

  • 考慮根據大小和時間進行緩衝做為更多案例實作批次處理的方式。

另請參閱

顯示:
© 2015 Microsoft