SQL Server預存程序的撰寫與最佳化方法

[導讀] 　　在資料庫的開發過程中，常常會遇到複雜的業務邏輯和對資料庫的操作，這個時候就會用SP來封裝資料庫操作。如果專案的SP較多，書寫又沒有一定的規範，將會影響以後的系統維護困難和大SP邏輯的難以理解，另外如

　　在資料庫的開發過程中，經常會遇到複雜的業務邏輯和對資料庫的操作，這個時候就會用SP來封裝資料庫操作。如果專案的SP較多，書寫又沒有一定的規範，將會影響以後的系統維護困難和大SP邏輯的難以理解，另外如果資料庫的資料量大或專案對SP的效能要求很，就會遇到優化的問題，否則速度有可能很慢，經過親身經驗，一個經過優化過的SP要比一個性能差的SP的效率甚至高幾百倍。
詳細內容：

1、開發人員如果用到其他庫的Table或View，務必在目前庫中建立View來實現跨庫操作，最好不要直接使用“databse.dbo.table_name”，因為sp_depends不能顯示該SP所使用的跨庫table或view，不方便校驗。

2、開發人員在提交SP前，必須已經使用set showplan on分析過查詢計劃，並做過自身的查詢最佳化檢查。

3、高程式運行效率，最佳化應用程序，在SP編寫過程中應該注意以下幾點：

(a)SQL的使用規範：

i.　盡量避免大事務操作，慎用holdlock子句，提高系統並發能力。

ii.　盡量避免重複存取同一張或幾張表，尤其是資料量較大的表，可以考慮先根據條件提取資料到臨時表中，然後再做連接。

iii.　盡量避免使用遊標，因為遊標的效率較差，如果遊標操作的資料超過1萬行，那麼就應該改寫;如果使用了遊標，就要盡量避免在遊標循環中再進行表格連接的操作。

iv.　注意where字句寫法，必須考慮語句順序，應該根據索引順序、範圍大小來確定條件子句的前後順序，盡可能的讓字段順序與索引順序相一致，範圍從大到小。

v.　不要在where子句中的「=」左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引。

vi.　盡量使用exists代替select count(1)來判斷是否存在記錄，count函數只有在統計表中所有行數時使用，而且count(1)比count(*)更有效率。

vii.　盡量使用“>=”，不要使用“>”。

viii.　注意一些or子句和union子句之間的替換

#ix.　注意表之間連接的資料類型，避免不同類型資料之間的連接。

x.　注意預存程序中參數與資料類型的關係。

xi.　注意insert、update運算的資料量，防止與其他應用衝突。如果資料量超過200個資料頁(400k)，那麼系統將會進行鎖定升級，頁級鎖會升級成表級鎖定。

(b)索引的使用規格：

i.　索引的建立要與應用程式結合考慮，建議大的OLTP表不要超過6個索引。

ii.　盡可能的使用索引欄位作為查詢條件，尤其是叢集索引，必要時可以透過index index_name來強制指定索引

iii.　避免對大表查詢時進行table scan，必要時可考慮新建索引。

iv.　在使用索引欄位作為條件時，如果該索引是聯合索引，那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，否則該索引將不會被使用。

v.　要注意索引的維護，週期性重建索引，重新編譯預存程序。

(c)tempdb的使用規格：

i.　盡量避免使用distinct、order by、group by、having、join、cumpute ，因為這些語句會加重tempdb的負擔。

ii.　避免經常建立和刪除臨時表，減少系統表資源的消耗。

iii.　在新臨時表時，如果一次插入資料量很大，那麼可以使用select into代替create table，避免log，提高速度;如果資料量不大，為了緩和系統表的資源，建議先create table，然後insert。

iv.　如果臨時表的資料量較大，需要建立索引，那麼應該將建立臨時表和建立索引的過程放在單獨一個子儲存過程中，這樣才能保證系統能夠很好的使用到該臨時表的索引。

v.　如果使用到了臨時表，在預存程序的最後務必將所有的臨時表明確刪除，先truncate table，然後drop table，這樣可以避免系統表的較長時間鎖定。

vi.　慎用大的臨時表與其他大表的連接查詢和修改，減低系統表負擔，因為這種操作會在一條語句中多次使用tempdb的系統表。

(d)合理的演算法使用：

根據上述已提及的SQL最佳化技術和ASE Tuning手冊中的SQL最佳化內容,結合實際應用,採用多種演算法進行比較,以獲得消耗資源最少、效率最高的方法。具體可用ASE調優指令：set statistics io on, set statistics time on , set showplan on 等。

以上是SQL Server預存程序的撰寫與最佳化方法的詳細內容。更多資訊請關注PHP中文網其他相關文章！