SQL Server存儲過(guò)程編寫(xiě)和優(yōu)化

一、適合讀者對象：數據庫開(kāi)發(fā)程序員，數據庫的數據量很多，涉及到對SP(存儲過(guò)程)的優(yōu)化的項目開(kāi)發(fā)人員，對數據庫有濃厚興趣的人。

　　二、介紹：在數據庫的開(kāi)發(fā)過(guò)程中，經(jīng)常會(huì )遇到復雜的業(yè)務(wù)邏輯和對數據庫的操作，這個(gè)時(shí)候就會(huì )用SP來(lái)封裝數據庫操作。如果項目的SP較多，書(shū)寫(xiě)又沒(méi)有一定的規范，將會(huì )影響以后的系統維護困難和大SP邏輯的難以理解，另外如果數據庫的數據量大或者項目對SP的性能要求很，就會(huì )遇到優(yōu)化的問(wèn)題，否則速度有可能很慢，經(jīng)過(guò)親身經(jīng)驗，一個(gè)經(jīng)過(guò)優(yōu)化過(guò)的SP要比一個(gè)性能差的SP的效率甚至高幾百倍。

　　三、內容：

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

　　2、開(kāi)發(fā)人員在提交

　　前，必須已經(jīng)使用set showplan on分析過(guò)查詢(xún)計劃，做過(guò)自身的查詢(xún)優(yōu)化檢查。

　　3、高程序運行效率，優(yōu)化應用程序，在SP編寫(xiě)過(guò)程中應該注意以下幾點(diǎn)：

　　a)SQL的使用規范：

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

　　ii.　盡量避免反復訪(fǎng)問(wèn)同一張或幾張表，尤其是數據量較大的表，可以考慮先根據條件提取數據到臨時(shí)表中，然后再做連接。

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

　　iv.　注意where字句寫(xiě)法，必須考慮語(yǔ)句順序，應該根據索引順序、范圍大小來(lái)確定條件子句的前后順序，盡可能的讓字段順序與索引順序相一致，范圍從大到小。

　　v.　不要在where子句中的“=”左邊進(jìn)行函數、算術(shù)運算或其他表達式運算，否則系統將可能無(wú)法正確使用索引。

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

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

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

　　ix.　注意表之間連接的數據類(lèi)型，避免不同類(lèi)型數據之間的連接。

　　x.　注意存儲過(guò)程中參數和數據類(lèi)型的關(guān)系。

　　xi.　注意insert、update操作的數據量，防止與其他應用沖突。如果數據量超過(guò)200個(gè)數據頁(yè)面(400k)，那么系統將會(huì )進(jìn)行鎖升級，頁(yè)級鎖會(huì )升級成表級鎖。

　　b)索引的使用規范：

　　i.　索引的創(chuàng )建要與應用結合考慮，建議大的OLTP表不要超過(guò)6個(gè)索引。

　　ii.　盡可能的使用索引字段作為查詢(xún)條件，尤其是聚簇索引，必要時(shí)可以通過(guò)index index_name來(lái)強制指定索引

　　iii.　避免對大表查詢(xún)時(shí)進(jìn)行table scan，必要時(shí)考慮新建索引。

　　iv.　在使用索引字段作為條件時(shí)，如果該索引是聯(lián)合索引，那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統使用該索引，否則該索引將不會(huì )被使用。

　　v.　要注意索引的維護，周期性重建索引，重新編譯存儲過(guò)程。

　　c) tempdb的使用規范：

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

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

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

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

　　v.　如果使用到了臨時(shí)表，在存儲過(guò)程的最后務(wù)必將所有的臨時(shí)表顯式刪除，先truncate table，然后drop table，這樣可以避免系統表的較長(cháng)時(shí)間鎖定。

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

　　d)合理的算法使用：

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