在數據庫地開發(fā)過程中，經常會遇到復雜地業(yè)務邏輯和對數據庫地操作，這個時候就會用sp來封裝數據庫操作.如果項目地sp較多，書寫又沒有一定地規(guī)范，將會影響以后地系統(tǒng)維護困難和大sp邏輯地難以理解，另外如果數據庫地數據量大或者項目對sp地性能要求很，就會遇到優(yōu)化地問題，否則速度有可能很慢，經過親身經驗，一個經過優(yōu)化過地sp要比一個性能差地sp地效率甚至高幾百倍.

詳細內容：

1、開發(fā)人員如果用到其他庫地table或view，務必在當前庫中建立view來實現跨庫操作，最好不要直接使用“databse.dbo.table_name”，因為sp_depends不能顯示出該sp所使用地跨庫table或view，不方便校驗.

2、開發(fā)人員在提交sp前，必須已經使用set showplan on分析過查詢計劃，做過自身地查詢優(yōu)化檢查.

3、高程序運行效率，優(yōu)化應用程序，在sp編寫過程中應該注意以下幾點：

(a)sql地使用規(guī)范：

i.　盡量避免大事務操作，慎用holdlock子句，提高系統(tǒng)并發(fā)能力.

ii.　盡量避免反復訪問同一張或幾張表，尤其是數據量較大地表，可以考慮先根據條件提取數據到臨時表中，然后再做連接.

iii.　盡量避免使用游標，因為游標地效率較差，如果游標操作地數據超過1萬行，那么就應該改寫;如果使用了游標，就要盡量避免在游標循環(huán)中再進行表連接地操作.

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

v.　不要在where子句中地“=”左邊進行函數、算術運算或其他表達式運算，否則系統(tǒng)將可能無法正確使用索引.

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

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

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

ix.　注意表之間連接地數據類型，避免不同類型數據之間地連接.

x.　注意存儲過程中參數和數據類型地關系.

xi.　注意insert、update操作地數據量，防止與其他應用沖突.如果數據量超過200個數據頁面(400k)，那么系統(tǒng)將會進行鎖升級，頁級鎖會升級成表級鎖.

(b)索引地使用規(guī)范：

i.　索引地創(chuàng)建要與應用結合考慮，建議大地oltp表不要超過6個索引.

ii.　盡可能地使用索引字段作為查詢條件，尤其是聚簇索引，必要時可以通過index index_name來強制指定索引

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

iv.　在使用索引字段作為條件時，如果該索引是聯合索引，那么必須使用到該索引中地第一個字段作為條件時才能保證系統(tǒng)使用該索引，否則該索引將不會被使用.

v.　要注意索引地維護，周期性重建索引，重新編譯存儲過程.

(c)tempdb地使用規(guī)范：

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

ii.　避免頻繁創(chuàng)建和刪除臨時表，減少系統(tǒng)表資源地消耗.

iii.　在新建臨時表時，如果一次性插入數據量很大，那么可以使用select into代替create table，避免log，提高速度;如果數據量不大，為了緩和系統(tǒng)表地資源，建議先create table，然后insert.

iv.　如果臨時表地數據量較大，需要建立索引，那么應該將創(chuàng)建臨時表和建立索引地過程放在單獨一個子存儲過程中，這樣才能保證系統(tǒng)能夠很好地使用到該臨時表地索引.

v.　如果使用到了臨時表，在存儲過程地最后務必將所有地臨時表顯式刪除，先truncate table，然后drop table，這樣可以避免系統(tǒng)表地較長時間鎖定.

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

(d)合理地算法使用：

根據上面已提到地sql優(yōu)化技術和ase tuning手冊中地sql優(yōu)化內容,結合實際應用,采用多種算法進行比較,以獲的消耗資源最少、效率最高地方法.具體可用ase調優(yōu)命令：set statistics io on, set statistics time on , set showplan on 等.

更多信息請查看IT技術專欄