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

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

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

三、內(nèi)容：

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

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

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

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

a)SQL的使用規(guī)范：

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

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

iii.盡量避免使用游標(biāo),因?yàn)橛螛?biāo)的效率較差,如果游標(biāo)操作的數(shù)據(jù)超過(guò)1萬(wàn)行,那么就應(yīng)該改寫(xiě)；如果使用了游標(biāo),就要盡量避免在游標(biāo)循環(huán)中再進(jìn)行表連接的操作。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

i.盡量避免使用distinct、order by、group by、having、join、cumpute,因?yàn)檫@些語(yǔ)句會(huì)加重tempdb的負(fù)擔(dān)。

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

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

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

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

vi.慎用大的臨時(shí)表與其他大表的連接查詢(xún)和修改,減低系統(tǒng)表負(fù)擔(dān),因?yàn)檫@種操作會(huì)在一條語(yǔ)句中多次使用tempdb的系統(tǒng)表。

d)合理的算法使用：

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