存儲過程編寫經驗和優化措施

在網友的部落格中看到這編文章不錯，就記了下來。供大家參考，在寫存儲過程時的經驗之談

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、***pute，因為這些語句會加重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 等

本文轉自yonghu86 51CTO部落格，原文連結：http://blog.51cto.com/yonghu/1321389，如需轉載請自行聯系原作者