針對.NET開發(fā)人員的存儲過程評估

2019-11-18 15:09:02

字體：大中小

供稿：網(wǎng)友

　　引言
　　本文旨在介紹使用存儲過程封裝應(yīng)用程序所需的 Transact-SQL (T-SQL) 的主要優(yōu)缺點(diǎn)，以便您對如何在您的環(huán)境中使用存儲過程做出明智的決策。對于那些可以利用存儲過程的人員，本文還介紹了與在 .NET 應(yīng)用程序中使用存儲過程相關(guān)的工具和最佳做法。
　　
　　在這方面，大家的看法并不一致，有些人認(rèn)為業(yè)務(wù)邏輯只應(yīng)被編碼到中間層或數(shù)據(jù)庫中；有些人認(rèn)為內(nèi)聯(lián)查詢是唯一的選擇；有些人則認(rèn)為存儲過程應(yīng)用于任何情況。所有這些方法都具有優(yōu)點(diǎn)和缺點(diǎn)。重要的是應(yīng)考慮，對您的應(yīng)用程序和環(huán)境至關(guān)重要的是什么。因此，讓我們了解一下存儲過程的概念，并考慮使用它們封裝 T-SQL 代碼的某些原因。
　　
　　考慮使用存儲過程的理由
　　也許您曾經(jīng)在多處編寫過使用 SqlCommand 對象的 T-SQL，但卻從未考慮過是否有一個比將它并入數(shù)據(jù)訪問代碼更好的位置。由于應(yīng)用程序隨著時間的推移增添了一些功能，因此其內(nèi)部可能包含一些復(fù)雜的 T-SQL 過程代碼。存儲過程為封裝此代碼提供了一個替換位置。
　　
　　大多數(shù)人可能對存儲過程已有所了解，但對于那些不了解存儲過程的人員而言，存儲過程是指一組作為單個代碼單元一起存儲于數(shù)據(jù)庫中的 T-SQL 語句。您可以使用輸入?yún)?shù)傳入運(yùn)行時信息，并取回作為結(jié)果集或輸出參數(shù)的數(shù)據(jù)。存儲過程在首次運(yùn)行時將被編譯。這將產(chǎn)生一個執(zhí)行計劃 - 實(shí)際上是 Microsoft® SQL Server™ 為在存儲過程中獲取由 T-SQL 指定的結(jié)果而必須采取的步驟的記錄。然后，執(zhí)行計劃在內(nèi)存中得到緩存，以備以后使用。這樣會改善存儲過程的性能，因?yàn)?SQL Server 無需為確定如何處理代碼而重新分析它，而只需引用緩存的計劃即可。這個緩存的計劃一直可用，直到 SQL Server 重新啟動，或直到它由于使用率較低而溢出內(nèi)存。
　　
　　性能
　　緩存的執(zhí)行計劃曾使存儲過程較之查詢更有性能優(yōu)勢。但對于 SQL Server 的幾個最新版本，執(zhí)行計劃已針對所有 T-SQL 批處理進(jìn)行了緩存，而不管它們是否在存儲過程中。因此，基于此功能的性能已不再是存儲過程的賣點(diǎn)。任何使用靜態(tài)語法，且提交頻率足以阻止執(zhí)行計劃溢出內(nèi)存的 T-SQL 批處理將會獲得同樣的性能好處。“靜態(tài)”部分是要害；任何更改，即使像添加注釋這樣無關(guān)緊要的更改，也將導(dǎo)致無法與緩存的計劃相匹配，從而將無法重復(fù)使用計劃。
　　
　　但是，當(dāng)存儲過程可以用于降低網(wǎng)絡(luò)流量時，它們?nèi)匀荒軌蛱峁┬阅芎锰帯Ｄ恍柙诰W(wǎng)絡(luò)中發(fā)送 EXECUTE stored_PRoc_name 語句，而非整個 T-SQL 例程，這可以在復(fù)雜操作中廣泛使用。設(shè)計良好的存儲過程可以將客戶端與服務(wù)器之間的許多往返過程簡化為單個調(diào)用。
　　
　　此外，使用存儲過程使您能夠增強(qiáng)對執(zhí)行計劃的重復(fù)使用，由此可以通過使用遠(yuǎn)程過程調(diào)用 (RPC) 處理服務(wù)器上的存儲過程而提高性能。使用 StoredProcedure 的 SqlCommand.CommandType 時，存儲過程通過 RPC 執(zhí)行。RPC 封裝參數(shù)和調(diào)用服務(wù)器端過程的方式使引擎能夠輕松地找到匹配的執(zhí)行計劃，并只需插入更新的參數(shù)值。
　　
　　考慮使用存儲過程提高性能時，最后要考慮是否要充分利用 T-SQL 的優(yōu)點(diǎn)。請考慮要如何處理數(shù)據(jù)。
　　
　　• 是否要使用基于集合的操作，或執(zhí)行 T-SQL 中完全支持的其他操作？那么存儲過程就是一個選擇，而內(nèi)聯(lián)查詢也可以使用。
　　
　　• 是否嘗試執(zhí)行基于行的操作，或復(fù)雜的字符串處理？那么可能要重新考慮在 T-SQL 中進(jìn)行這種處理，這不包括使用存儲過程，至少要到 Yukon 發(fā)布并且公共語言運(yùn)行庫 (CLR) 集成可用后，才能使用存儲過程。
　　
　　可維護(hù)性和抽象
　　要考慮的另一個潛在優(yōu)勢是可維護(hù)性。理想情況下，數(shù)據(jù)庫架構(gòu)從不更改，業(yè)務(wù)規(guī)則不被修改，但在現(xiàn)實(shí)環(huán)境中，情況則完全不同。既然情況如此，那么假如可以修改存儲過程以包括新 X、Y 和 Z 表（為支持新的銷售活動而添加了這些表）中的數(shù)據(jù)，而不是在應(yīng)用程序代碼中的某個位置更改此信息，則維護(hù)對您來說可能比較輕易。在存儲過程中更改此信息使得更新對應(yīng)用程序而言具有透明性 - 您仍然返回相同的銷售信息，即使存儲過程的內(nèi)部實(shí)現(xiàn)已經(jīng)更改。更新存儲過程通常比更改、測試以及重新部署程序集需要較少的時間和精力。
　　
　　另外，通過抽象化實(shí)現(xiàn)并將此代碼保存在存儲過程中，任何需要訪問數(shù)據(jù)的應(yīng)用程序均可以獲取一致的數(shù)據(jù)。您無需在多個位置維護(hù)相同的代碼，用戶便可獲取一致的信息。
　　
　　在存儲過程中存儲 T-SQL 的另一個可維護(hù)性優(yōu)點(diǎn)是更好的版本控制。您可以對創(chuàng)建和修改存儲過程的腳本進(jìn)行版本控制，就像可以對任何其他源代碼模塊進(jìn)行版本控制一樣。通過使用 Microsoft Visual SourceSafe® 或某個其他源代碼控制工具，您可以輕松地恢復(fù)到或引用舊版本的存儲過程。
　　
　　在使用存儲過程提高可維護(hù)性時應(yīng)值得注重的一點(diǎn)是，它們無法阻止您對架構(gòu)和規(guī)則進(jìn)行所有可能的更改。假如更改范圍大到需要對輸入存儲過程的參數(shù)進(jìn)行更改，或者要更改由其返回的數(shù)據(jù)，則您仍需要更新程序集中的代碼以添加參數(shù)、更新 GetValue() 調(diào)用，等等。
　　
　　要注重的另一個問題是，由于存儲過程將應(yīng)用程序綁定到 SQL Server，因此使用存儲過程封裝業(yè)務(wù)邏輯將限制應(yīng)用程序的可移植性。假如應(yīng)用程序的可移植性在您的環(huán)境中非常重要，則將業(yè)務(wù)邏輯封裝在不特定于 RDBMS 的中間層中可能是一個更佳的選擇。
　　
　　安全性
　　考慮使用存儲過程的最終原因是它們可用于增強(qiáng)安全性。
　　
　　就治理用戶對信息的訪問而言，通過向用戶授予對存儲過程（而不是基礎(chǔ)表）的訪問權(quán)限，它們可以提供對特定數(shù)據(jù)的訪問。您可以將存儲過程看成是 SQL Server 視圖（假如您對它們熟悉的話），除非存儲過程接受用戶的輸入以動態(tài)更改顯示的數(shù)據(jù)。
　　
　　存儲過程還可以幫助您解決代碼安全問題。它們可以防止某些類型的 SQL 插入攻擊 - 主要是一些使用運(yùn)算符（如 AND 或 OR）將命令附加到有效輸入?yún)?shù)值的攻擊。在應(yīng)用程序受到攻擊時，存儲過程還可以隱藏業(yè)務(wù)規(guī)則的實(shí)現(xiàn)。這對于將此類信息視為知識產(chǎn)權(quán)的公司非常重要。
　　
　　另外，使用存儲過程使您可以使用 ADO.NET 中提供的 SqlParameter 類指定存儲過程參數(shù)的數(shù)據(jù)類型。這為驗(yàn)證用戶提供的值類型（作為深層次防御性策略的一部分）提供了一個簡單方法。在縮小可接受用戶輸入的范圍方面，參數(shù)在內(nèi)聯(lián)查詢中與在存儲過程中一樣有用。
　　
　　使用存儲過程增強(qiáng)安全性時值得注重的是，糟糕的安全性或編碼做法仍然會使您受到攻擊。對 SQL Server 角色創(chuàng)建和分配假如不加注重將導(dǎo)致人們訪問到不應(yīng)看到的數(shù)據(jù)。同時，假如認(rèn)為使用存儲過程便可防止所有 SQL 插入代碼攻擊（例如，將數(shù)據(jù)操作語言 (DML) 附加到輸入?yún)?shù)），后果將是一樣的。
　　
　　另外，無論 T-SQL 位于代碼還是位于存儲過程中，使用參數(shù)進(jìn)行數(shù)據(jù)類型驗(yàn)證都不是萬無一失的。所有用戶提供的數(shù)據(jù)（尤其是文本數(shù)據(jù)）在傳遞到數(shù)據(jù)庫之前都應(yīng)受到附加的驗(yàn)證。
　　
　　存儲過程對我是否適用？
　　或許適合吧。讓我們概括一下它們的優(yōu)點(diǎn)：
　　
　　• 通過降低網(wǎng)絡(luò)流量提高性能
　　
　　• 提供單點(diǎn)維護(hù)
　　
　　• 抽象化業(yè)務(wù)規(guī)則，以確保一致性和安全性
　　
　　• 通過將某些形式的攻擊降至最低，以增強(qiáng)安全性
　　
　　• 支持執(zhí)行計劃重復(fù)使用
　　
　　假如您的環(huán)境答應(yīng)利用存儲過程提供的好處（如上所述），強(qiáng)烈建議使用它們。對于改進(jìn)數(shù)據(jù)在環(huán)境中的處理方式而言，它們提供了一個很好的工具。另一方面，假如您的環(huán)境中存在可移植性、大量使用非 T-SQL 友好的進(jìn)程或者不穩(wěn)定的數(shù)據(jù)庫架構(gòu)等削弱這些優(yōu)點(diǎn)的因素，則您可能要考慮其他方法。
　　
　　另一個要注重的事項(xiàng)是機(jī)構(gòu)內(nèi)部所擁有的 T-SQL 專業(yè)人員的數(shù)量。您有足夠的 T-SQL 知識嗎？您愿意學(xué)習(xí)嗎？或者，您有 DBA 或合適的人員幫您編寫存儲過程嗎？把握的 T-SQL 知識越多，存儲過程就會越好，維護(hù)它們就會越輕易。例如，T-SQL 主要用于基于集合的操作，而不是基于行的操作。依靠于光標(biāo)（因?yàn)樗鼈兿蚰崾緮?shù)據(jù)集）將導(dǎo)致性能降低。假如您不太了解 T-SQL，請將本文作為一次學(xué)習(xí)機(jī)會。無論您將它用在何處，本文介紹的知識都將改善您的代碼。
　　
　　因此，假如您認(rèn)為存儲過程會為應(yīng)用程序增添非凡的效果，請繼續(xù)閱讀本文。我們將回顧一些簡化存儲過程使用的工具，并了解一些創(chuàng)建存儲過程的最佳做法。
　　
　　Visual Studio .NET 工具
　　Microsoft Visual Studio® .NET 提供了一些工具，使您能夠查看和操作 SQL Server 存儲過程（以及其他數(shù)據(jù)庫對象）。讓我們簡要介紹一下您可能期望獲得的功能。
　　
　　查看存儲過程
　　您可以使用服務(wù)器資源治理器查看現(xiàn)有的存儲過程、查看它們所需的參數(shù)或查看它們的內(nèi)部實(shí)現(xiàn)。假如您已經(jīng)連接到安裝了 SQL Server 的服務(wù)器，則可以依次展開“ServerName”、“SQL Server”、“SQLInstanceName”、“Northwind”、“存儲過程”，并展開“CustOrdersDetail”。該資源治理器將顯示存儲過程所需的任何參數(shù)及其返回的任何列。假如簽出以上任何項(xiàng)的屬性，便會發(fā)現(xiàn)數(shù)據(jù)類型表示為 ADO 類型。框架文檔提供了這些類型與 .NET 類型之間的簡單映射。當(dāng)然，在 ADO.NET 代碼中使用參數(shù)時，可能要將它們的數(shù)據(jù)類型表示為 SqlDBType 枚舉的成員。您會看到 .NET 類型與 SqlDbType 類型之間的映射。
　　
　　假如雙擊存儲過程，Visual Studio 將會在 SQL 編輯器中打開它，其中顯示了便于查看的顏色編碼和所有內(nèi)容。注重，它并不顯示 CREATE PROCEDURE 語法（該語法實(shí)際上在數(shù)據(jù)庫中已存在），由于它假定您要修改存儲過程，因此提供了 ALTER PROCEDURE 語法。
　　創(chuàng)建和修改存儲過程
　　您具有在相應(yīng)

上一篇：RMI規(guī)范--第六章

下一篇：RMI規(guī)范--第二章