預存程序的好處和一些注意事項

來源:互聯網
上載者:User
預存程序的好處和一些注意事項

作者:xue560出處:部落格2011-09-26 13:51

  預存程序天天用,關於使用預存程序的sql語句的爭論也一直在,個人覺得使用預存程序要好於用sql語句,整理了一些說明:

  預存程序是由一些SQL語句和控制語句組成的被封裝起來的過程,它駐留在資料庫中,可以被客戶應用程式調用,也可以從另一個過程或觸發器調用。它的參數可以被傳遞和返回。與應用程式中的函數過程類似,預存程序可以通過名字來調用,而且它們同樣有輸入參數和輸出參數。

  根據傳回值類型的不同,我們可以將預存程序分為三類:返回記錄集的預存程序, 返回數值的預存程序(也可以稱為標量預存程序),以及行為預存程序。顧名思義,返回記錄集的預存程序的執行結果是一個記錄集,典型的例子是從資料庫中檢索出符合某一個或幾個條件的記錄;返回數值的預存程序執行完以後返回一個值,例如在資料庫中執行一個有傳回值的函數或命令;最後,行為預存程序僅僅是用來實現資料庫的某個功能,而沒有傳回值,例如在資料庫中的更新和刪除操作。

 

使用預存程序的好處

  相對於直接使用SQL語句,在應用程式中直接調用預存程序有以下好處:

  (1)減少網路通訊量。調用一個行數不多的預存程序與直接調用SQL語句的網路通訊量可能不會有很大的差別,可是如果預存程序包含上百行SQL語句,那麼其效能絕對比一條一條的調用SQL語句要高得多。

  (2)執行速度更快。有兩個原因:首先,在預存程序建立的時候,資料庫已經對其進行了一次解析和最佳化。其次,預存程序一旦執行,在記憶體中就會保留一份這個預存程序,這樣下次再執行同樣的預存程序時,可以從記憶體中直接調用。

  (3)更強的適應性:由於預存程序對資料庫的訪問是通過預存程序來進行的,因此資料庫開發人員可以在不改動預存程序介面的情況下對資料庫進行任何改動,而這些改動不會對應用程式造成影響。

  (4) 布式工作:應用程式和資料庫的編碼工作可以分別獨立進行,而不會相互壓制。

  msdn上面相關的說明

 

考慮使用預存程序的理由

  也許您曾經在多處編寫過使用 SqlCommand 對象的 T-SQL,但卻從未考慮過是否有一個比將它併入資料存取碼更好的位置。由於應用程式隨著時間的推移增添了一些功能,因此其內部可能包含一些複雜的 T-SQL 過程代碼。預存程序為封裝此代碼提供了一個替換位置。

  大多數人可能對預存程序已有所瞭解,但對於那些不瞭解預存程序的人員而言,預存程序是指一組作為單個代碼單元一起儲存於資料庫中的 T-SQL 陳述式。您可以使用輸入參數傳入運行時資訊,並取回作為結果集或輸出參數的資料。預存程序在首次運行時將被編譯。這將產生一個執行計畫 - 實際上是 Microsoft SQL Server 為在預存程序中擷取由 T-SQL 指定的結果而必須採取的步驟的記錄。然後,執行計畫在記憶體中得到緩衝,以備以後使用。這樣會改善預存程序的效能,因為 SQL Server 無需為確定如何處理代碼而重新分析它,而只需引用緩衝的計劃即可。這個緩衝的計劃一直可用,直到 SQL Server 重新啟動,或直到它由於使用率較低而溢出記憶體。

 效能

  緩衝的執行計畫曾使預存程序較之查詢更有效能優勢。但對於 SQL Server 的幾個最新版本,執行計畫已針對所有 T-SQL 批處理進行了緩衝,而不管它們是否在預存程序中。因此,基於此功能的效能已不再是預存程序的賣點。任何使用靜態文法,且提交頻率足以阻止執行計畫溢出記憶體的 T-SQL 批處理將會獲得同樣的效能好處。“靜態”部分是關鍵;任何更改,即使像添加註釋這樣無關緊要的更改,也將導致無法與緩衝的計劃相匹配,從而將無法重複使用計劃。

  但是,當預存程序可以用於降低網路流量時,它們仍然能夠提供效能好處。您只需在網路中發送 EXECUTE stored_proc_name 語句,而非整個 T-SQL 常式,這可以在複雜操作中廣泛使用。設計良好的預存程序可以將用戶端與伺服器之間的許多往返過程簡化為單個調用。

  此外,使用預存程序使您能夠增強對執行計畫的重複使用,由此可以通過使用遠端程序呼叫 (RPC) 處理伺服器上的預存程序而提高效能。使用 StoredProcedure 的 SqlCommand.CommandType 時,預存程序通過 RPC 執行。RPC 封裝參數和調用伺服器端過程的方式使引擎能夠輕鬆地找到匹配的執行計畫,並只需插入更新的參數值。

  考慮使用預存程序提高效能時,最後要考慮是否要充分利用 T-SQL 的優點。請考慮要如何處理資料。

  •是否要使用基於集合的操作,或執行 T-SQL 中完全支援的其他動作?那麼預存程序就是一個選擇,而內聯查詢也可以使用。

  •是否嘗試執行基於行的操作,或複雜的字串處理?那麼可能要重新考慮在 T-SQL 中進行這種處理,這不包括使用預存程序,至少要到 Yukon 發布並且公用語言運行庫 (CLR) 整合可用後,才能使用預存程序。

 可維護性和抽象

  要考慮的另一個潛在優勢是可維護性。理想情況下,資料庫結構描述從不更改,商務規則不被修改,但在現實環境中,情況則完全不同。既然情況如此,那麼如果可以修改預存程序以包括新 X、Y 和 Z 表(為支援新的銷售活動而添加了這些表)中的資料,而不是在應用程式代碼中的某個位置更改此資訊,則維護對您來說可能比較容易。在預存程序中更改此資訊使得更新對應用程式而言具有透明性 - 您仍然返回相同的銷售資訊,即使預存程序的內部實現已經更改。更新預存程序通常比更改、測試以及重新部署程式集需要較少的時間和精力。

  另外,通過抽象化實現並將此代碼儲存在預存程序中,任何需要訪問資料的應用程式均可以擷取一致的資料。您無需在多個位置維護相同的代碼,使用者便可擷取一致的資訊。

  在預存程序中儲存 T-SQL 的另一個可維護性優點是更好的版本控制。您可以對建立和修改預存程序的指令碼進資料列版本設定,就像可以對任何其他原始碼模組進資料列版本設定一樣。通過使用 Microsoft Visual SourceSafe 或某個其他原始碼控制工具,您可以輕鬆地恢複到或引用舊版本的預存程序。

  在使用預存程序提高可維護性時應值得注意的一點是,它們無法阻止您對架構和規則進行所有可能的更改。如果更改範圍大到需要對輸入預存程序的參數變更,或者要更改由其返回的資料,則您仍需要更新程式集中的代碼以添加參數、更新 GetValue() 調用,等等。

  要注意的另一個問題是,由於預存程序將應用程式綁定到 SQL Server,因此使用預存程序封裝商務邏輯將限制應用程式的可移植性。如果應用程式的可移植性在您的環境中非常重要,則將商務邏輯封裝在不特定於 RDBMS 的中介層中可能是一個更佳的選擇。

  安全性

  考慮使用預存程序的最終原因是它們可用於增強安全性。

  就系統管理使用者對資訊的訪問而言,通過向使用者授予對預存程序(而不是基礎資料表)的存取權限,它們可以提供對特定資料的訪問。您可以將預存程序看成是 SQL Server 視圖(如果您對它們熟悉的話),除非預存程序接受使用者的輸入以動態更改顯示的資料。

  預存程序還可以協助您解決代碼安全問題。它們可以防止某些類型的 SQL 插入攻擊 - 主要是一些使用運算子(如 AND 或 OR)將命令附加到有效輸入參數值的攻擊。在應用程式受到攻擊時,預存程序還可以隱藏商務規則的實現。這對於將此類資訊視為智慧財產權的公司非常重要。

  另外,使用預存程序使您可以使用 ADO.NET 中提供的 SqlParameter 類指定預存程序參數的資料類型。這為驗證使用者提供的實值型別(作為深層次防禦性策略的一部分)提供了一個簡單方法。在縮小可接受使用者輸入的範圍方面,參數在內聯查詢中與在預存程序中一樣有用。

  使用預存程序增強安全性時值得注意的是,糟糕的安全性或編碼做法仍然會使您受到攻擊。對 SQL Server 角色建立和分配如果不加註意將導致人們訪問到不應看到的資料。同時,如果認為使用預存程序便可防止所有 SQL 插入代碼攻擊(例如,將資料操作語言 (DML) 附加到輸入參數),後果將是一樣的。

  另外,無論 T-SQL 位於代碼還是位於預存程序中,使用參數進行資料類型驗證都不是萬無一失的。所有使用者提供的資料(尤其是文本資料)在傳遞到資料庫之前都應受到附加的驗證。

  預存程序對我是否適用?

  或許適合吧。讓我們概括一下它們的優點:

  •通過降低網路流量提高效能

  •提供單點維護

  •抽象化商務規則,以確保一致性和安全性

  •通過將某些形式的攻擊降至最低,以增強安全性

  •支援執行計畫重複使用

 

  如果您的環境允許利用預存程序提供的好處(如上所述),強烈建議使用它們。對於改進資料在環境中的處理方式而言,它們提供了一個很好的工具。另一方面,如果您的環境中存在可移植性、大量使用非 T-SQL 友好的進程或者不穩定的資料庫結構描述等削弱這些優點的因素,則您可能要考慮其他方法。

  另一個要注意的事項是機構內部所擁有的 T-SQL 專業人員的數量。您有足夠的 T-SQL 知識嗎?您願意學習嗎?或者,您有 DBA 或合適的人員幫您編寫預存程序嗎?掌握的 T-SQL 知識越多,預存程序就會越好,維護它們就會越容易。例如,T-SQL 主要用於基於集合的操作,而不是基於行的操作。依賴於游標(因為它們向您提示資料集)將導致效能降低。如果您不太瞭解 T-SQL,請將本文作為一次學習機會。無論您將它用在何處,本文介紹的知識都將改善您的代碼。

  因此,如果您認為預存程序會為應用程式增添特殊的效果,請繼續閱讀本文。我們將回顧一些簡化預存程序使用的工具,並瞭解一些建立預存程序的最佳做法。

 注意事項

  如果要開始建立與應用程式一起使用的預存程序,應記住下面這些提示,以便兩者正常運行並良好地配合工作。

  使用 SET NOCOUNT ON

  預設情況下,預存程序將返回過程中每個語句影響的行數。如果不需要在應用程式中使用該資訊(大多數應用程式並不需要),請在預存程序中使用 SET NOCOUNT ON 語句以終止該行為。根據預存程序中包含的影響行的語句的數量,這將刪除用戶端和伺服器之間的一個或多個往返過程。儘管這不是大問題,但它可以為高流量應用程式的效能產生負面影響。

  create procedure test_MyStoredProc @param1 intasset nocount on

  不要使用 sp_ prefix

  sp_ prefix 是為系統預存程序保留的。資料庫引擎將始終首先在主要資料庫中尋找具有此首碼的預存程序。這意味著當引擎首先檢查主要資料庫,然後檢查預存程序實際所在的資料庫時,將需要較長的時間才能完成檢查過程。而且,如果碰巧存在一個名稱相同的系統預存程序,則您的過程根本不會得到處理。

盡量少用選擇性參數

  在頻繁使用選擇性參數之前,請仔細考慮。通過執行額外的工作會很輕易地影響效能,而根據為任意指定執行輸入的參數集合,這些工作時不需要的。您可以通過對每種可能的參數組合使用條件編碼來解決此問題,但這相當費時並會增大出錯的幾率。

  在可能的情況下使用 OUTPUT 參數

  通過使用 OUTPUT 參數返回標量資料,可以略微提高速度並節省少量的處理功率。在應用程式需要返回單個值的情況下,請嘗試此方法,而不要將結果集具體化。在適當的情況下,也可以使用 OUTPUT 參數返回游標,但是我們將在後續文章中介紹游標處理與基於集合的處理在理論上的分歧。

  提供傳回值

  使用預存程序的傳回值,將處理狀態資訊返回給進行調用的應用程式。在您的開發組中,將一組傳回值及其含義標準化,並一致地使用這些值。這會使得處理調用應用程式中的錯誤更加容易,並向終端使用者提供有關問題的有用資訊。

  首先使用 DDL,然後使用 DML

  將 DML 語句放在資料定義語言 (Data Definition Language) (DDL) 語句之後執行(此時 DML 將引用 DDL 修改的任意對象)時,SQL Server 將重新編譯預存程序。出現這種情況,是由於為了給 DML 建立計劃,SQL Server 需要考慮由 DDL 對該對象所作的更改。如果留意預存程序開頭的所有 DDL,則它只需重新編譯一次。如果將 DDL 和 DML 語句混合使用,則將強制預存程序多次進行重新編譯,這將對效能造成負面影響。

  始終使用注釋

  您可能不會始終維護此代碼。但其他人員將來可能想要瞭解它的用途。'Nuff 曾經這樣說。

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.