現在讓我們從編程模型和性能這兩個方面，看看如何用 CLR 來編寫訪問數據庫的業務邏輯。

編程模型

  使用 T-SQL，只是在過程代碼內部嵌入查詢語言語句 (SELECT/INSERT/UPDATE/ DELETE)。通過托管代碼，可以使用 SQL Server 托管提供程序來實現 Microsoft ADO.NET 數據訪問 API（也稱為 in-proc ADO.NET）。使用這種方法，可以將查詢語言語句（SELECT 和 DML 語句）作為動態字符串嵌入，并且將其作為參數傳遞給 ADO.NET API。與 T-SQL 的靜態方法相比，過程代碼內嵌入的 SQL 語句的基于動態 API 的特性是它們在編程模型上的主要區別。不利的是，in-proc ADO.NET 模型會產生比 T-SQL 更冗長的代碼。此外，因為 SQL 語句是動態字符串，所以在執行之前不在語法或語義上進行編譯和驗證。有利的是，帶有 ADO.NET 的數據庫編程模型與客戶端或中間層中使用的模型相似，因而更容易在各層之間移動代碼和利用現有的技術。

  此外，在基于 T-SQL 和基于 CLR 的編程模型中使用的都是同一 SQL 查詢語言，不同之處在于過程部分，注意到這一點是非常重要的。

性能

  正如已經提到的，在談及過程代碼、計算等方面時，與 T-SQL 相比，托管代碼在性能方面具有決定性的優勢。然而，對于數據訪問方面，T-SQL 在性能方面通常會更好。因此，通用規則是用 CLR 編寫計算和邏輯密集的代碼要比數據訪問密集的代碼好。不過這值得更詳細地考慮。

  讓我們看看數據訪問編程中的一些典型的基元和模式，以及在這些情況下如何使用 ADO.NET 進行 T-SQL 和托管編程。 

  將結果發送到客戶端

  這包括將一組行發送到客戶端，而沒有在服務器中“消費”它們（即沒有在例程內導航行）。使用 T-SQL，只需在 T-SQL proc 中嵌入一個 SELECT 語句就可以將 SELECT 產生的行發送到客戶端。通過托管代碼，可以使用 SqlPipe 對象將結果發送到客戶端。T-SQL 和 in-proc ADO.NET 平臺在這種情況下的作用是一樣的。

  提交 SQL 語句

  這包括來自過程代碼的 SQL 語句的執行往返。在這種情況下，T-SQL 具有很大的優勢（比 in-proc ADO.NET 快兩倍多）。

  此處需要重點注意的是，之所以在 CLR 中出現性能降低，是因為增加了額外的代碼層，包括將來自托管代碼的 T-SQL 語句提交給原生 SQL 代碼。在將該語句提交給查詢處理器之后，基于語句源（T-SQL 或 in-proc ADO.NET）的性能方面就沒有什么不同了。

  注：典型的數據訪問密集的存儲過程可能涉及提交一系列的 SQL 語句。如果 SQL 語句簡單，并且不需要花費大量的時間執行，則來自托管代碼的調用開銷可能占用大部分執行時間，這樣的過程用 T-SQL 編寫將執行得更好。

  只進、只讀行導航

  這包括以只進、只讀方式一次導航一個由 SQL 語句產生的行。在 T-SQL 中，這是通過只進、只讀光標實現的。在 CLR 中，這是通過 SqlDataReader 實現的。通常，每一條語句都涉及一些處理。如果忽略了與每行相關聯的處理，則導航行在 CLR 中就比在 T-SQL 光標中稍慢。然而，如果您關心為每行執行的處理，則 CLR 會更有優勢，因為 CLR 在這種處理上比 T-SQL 做得好。

  帶有更新的行導航

  如果需要根據光標中的當前位置更新行，則沒有相關的性能比較，因為 in-proc ADO.NET 不支持此功能，而應該通過 T-SQL 可更新光標來進行此操作。

  注 在任何可能的情況下，最好使用 UPDATE 語句來批量更新行，只有在這樣的修改無法用單一的 UPDATE 語句進行表示時，才應使用基于光標導航的 UPDATE。

  以下示例說明在特定情況下如何確定 T-SQL 和 CLR 將執行：

1.

  考慮這樣一個過程，它執行一系列（或在最簡單的情況下僅一個）INSERT/UPDATE/DELETE/SELECT 語句，帶有幾個或者不帶返回到客戶端的行，并且不導航 SELECT 產生的行。如果將這樣的過程編寫成 T-SQL 過程可能執行得更好。

2.

  考慮這樣一個過程，它執行單一的 SELECT 語句，并且使用存儲過程內的行，方法是，一次導航一行并進行一些不涉及對每行進行更多的數據訪問的處理。這個過程可能在帶有 in-proc ADO.NET 的 CLR 中執行得更好，特別是如果每行都有一些大量處理的開銷時（因為這樣的操作在 CLR 中比在 T-SQL 中更加高效）。

  下面是一些簡單的指導原則，可以用來在 CLR 和 T-SQL 之間進行選擇： 

  盡可能使用帶有 T-SQL SELECT、INSERT、UPDATE 和 DELETE 語句的基于集的處理。只有在無法使用基于集的 DML 語句之一表示邏輯時，才應該使用過程和基于行的處理。 

  如果過程僅僅是一個通過封裝基本 INSERT/UPDATE/DELETE/SELECT 操作訪問基表的包裝，則應該用 T-SQL 進行編寫。 

  如果過程主要包括結果集中的只進、只讀行導航，以及一些涉及每行的處理，則用 CLR 編寫可能更有效。 

  如果過程包括大量的數據訪問以及計算和邏輯，則可以考慮將過程代碼分隔為 CLR 來調用 T-SQL 過程，以進行大部分的數據訪問（反之亦然）。另一個替代方法是，使用單一的 T-SQL 批處理，它包括從托管代碼執行一次的一組查詢，以減少從托管代碼提交 T-SQL 語句的往返次數。

  后面的部分將更深入地討論在處理結果集時何時及如何適當地使用 T-SQL 和 CLR。
  CLR 與 XP

  在 SQL Server 以前的版本中，擴展存儲過程 (XP) 為數據庫程序開發人員提供了唯一可用的機制來編寫服務器端邏輯，這要么難于表示，要么不可能用 T-SQL 編寫。CLR 集成提供了一種更健壯的替代方法來編寫這種存儲過程。此外，使用 CLR 集成，過去以存儲過程形式編寫的邏輯通?？梢愿玫乇硎緸楸碇岛瘮?，因為它們允許它們允許將該函數構造的結果放在 SELECT 語句中進行查詢（通過將這些結果嵌入到 FROM 子句中）。

  以下是使用 CLR 過程或函數與 XP 相比的優勢： 


  粒度控制：很少可以控制 XP 能做什么或者不能做什么。使用代碼訪問安全模型，SQL Server 管理員可以分配三種權限之一：SAFE、EXTERNAL_ACCESS 或 UNSAFE，從而對托管代碼允許進行的操作集進行不同程序的控制。 

  可靠性：托管代碼（特別是在 SAFE 和 EXTERNAL_ACCESS 權限集中）提供了比 XP 更安全、更可靠的編程模型?？沈炞C的托管代碼確保了所有對對象的訪問都是通過強類型化的接口實現的，從而降低了程序訪問或破壞屬于 SQL Server 的內存緩沖的可能性。 

  數據訪問：使用 XP£¬編程人員必須向后顯式連接到數據庫（稱為回環），以訪問本地 SQL Server 數據庫。而且，必須將此回環連接顯式綁定到原來的會話事務上下文，以確保 XP 可以參與到調用它的同一個事務中。通過托管代碼，可以使用更自然和更有效的編程模型來訪問本地數據，這些模型利用當前的連接和事務上下文。 

  性能：System.Data.SqlServer API 允許托管過程將結果集發送回客戶端，其性能比 XP 使用的開放式數據服務 (ODS) API 更好。此外，System.Data.SqlServer API 支持新的數據類型（如 SQL Server 2005 中引入的 XML、(n)varchar(max)、varbinary(max)），而沒有擴展 ODS API 來支持這些新的數據類型。 

  可伸縮性：通過托管代碼，SQL Server 可以管理資源（如內存、線程和同步）的使用，因為公開這些資源的托管 API 是在 SQL Server 資源管理器上實現的。相反，SQL Server 不能查看或控制 XP 的資源使用情況。舉例來說，如果 XP 消耗了太多的 CPU 或內存資源，就沒有辦法使用 SQL Server 來檢測或控制。然而，通過托管代碼，SQL Server 可以檢測到特定線程在一段很長的時間內一直沒有退出，然后就強制該任務退出，這樣其他工作可以按計劃進行。因此，使用托管代碼提供了更好的可伸縮性和健壯性。

  正如上面所提到的，在數據訪問和發送結果集給客戶端方面，CLR 過程比 XP 做得更好。對于不包括數據訪問和發送結果的代碼，比較 XP 和托管代碼的性能就是比較托管代碼和原生代碼的性能。一般來說，在這些情況下托管代碼比不上原生代碼的性能。而且，當在 SQL Server 內運行時，從托管代碼到原生代碼的事務處理過程有額外的開銷，因為在移動到原生代碼和從原生代碼移回時，SQL Server 需要對特定于線程的設置進行額外的登記-保留。因此，對于在托管代碼和原生代碼之間有頻繁的事務處理的情況，XP 大大地勝過在 SQL Server 內部運行的托管代碼。

  對于大多數擴展過程，如果考慮數據訪問和結果發送的可伸縮性、可靠性和性能優勢，CLR 過程提供了更好的替代方法。對于性能主要是由處理（數據訪問和結果發送之外的）和頻繁的托管-原生轉換決定的情況，應該權衡 CLR 的可伸縮性和可靠性的優勢與 XP 的原始性能優勢。

 四、代碼位置：數據庫與中間層

  通過在數據庫中提供豐富的編程模型，CLR 集成提供了將邏輯從其他層移動到數據庫層的選擇。然而，這顯然并不意味著所有或大部分邏輯應該移到數據庫中。

  將邏輯移到數據庫層可以減少網絡中的數據流量，但是增加了服務器上寶貴的 CPU 資源的負荷。因此，在應用程序中做出代碼放置的決定之前，要慎重權衡。以下注意事項適用于將數據庫層作為首選的代碼位置： 

   數據驗證：在數據層進行數據驗證的邏輯可以更好地將數據和邏輯封裝在一起。這樣避免了在不同數據接觸點（如：后端處理、批量上載和來自中間層的數據更新等）中重復驗證邏輯。 

  減少網絡流量：對于需要處理大量的數據而產生很少的數據的數據處理任務（如數據分析應用程序中的需求預測、基于需求預測的生產安排等）來說，將邏輯放在數據庫層中是合適的。

  注即使在引入 CLR 支持之前，上面的注意事項也是有效的。數據庫層中的 CLR 支持意味著編程語言的選擇沒有妨礙代碼位置的正確選擇。

  示例：生產安排

  生產安排是制造企業的常見任務。在高層次上，它包括制訂何時生產多少單位數量的產品的計劃，以便能夠滿足需求、最大程度的降低庫存成本，同時將生產成本降到最低。有幾個算法將需求預測、庫存成本和生產線安裝成本作為輸入，而將制造策略作為輸出。

  假定將來的需求預測存儲在 SQL Server 表中，則此類算法的實現有以下特征：

1.


  使用大量的數據作為輸入（如需求預測）。

2.


  產生小結果（如在特定的日期內生產的單位數量）。

3.


  需要相當多的計算以便從輸入中派生輸出。

  在中間層實現這樣的算法是可行的，但是在數據庫外移動輸入集方面有性能損失。在 T-SQL 中將其實現為存儲過程也是可行的，但是因為需要復雜的計算，性能損失就顯現出來了。性能特征將隨著實際的數據量和算法的復雜性的不同而不同。

  為了驗證 CLR 集成是否適合于這樣的情況，我們舉一個特定的生產安排算法的示例 － Wagner-Whitin 算法的動態編程實現。正如所預料的，CLR 集成優于 T-SQL。對于這種情況，使用托管代碼還有其他好處。這種算法的實現需要使用大量的一維和多維數組、數據結構，而這些在 T-SQL 中是不可用的?？傊?，CLR 集成的性能要優于 T-SQL 實現幾個數量級。

  假定以下簡單的數據庫架構跟蹤可以生產的產品列表。

下表存儲了每周每個產品的需求預測信息。

  給定一組產品，它們的庫存和啟動成本以及未來需求預測，我們創建了接受如下輸入參數的存儲過程：1)制訂生產進度表的日期，2)按進度表生產所需要的周數。

存儲過程返回帶有下表中的架構的行集。