sql server存儲過程的編寫和優(yōu)化措施
來源:易賢網(wǎng) 閱讀:776 次 日期:2014-10-08 09:07:40
溫馨提示:易賢網(wǎng)小編為您整理了“sql server存儲過程的編寫和優(yōu)化措施”,方便廣大網(wǎng)友查閱!

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

詳細(xì)內(nèi)容:

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

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

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

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

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

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

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

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

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

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

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

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

ix. 注意表之間連接地?cái)?shù)據(jù)類型,避免不同類型數(shù)據(jù)之間地連接.

x. 注意存儲過程中參數(shù)和數(shù)據(jù)類型地關(guān)系.

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

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

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

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

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

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

v. 要注意索引地維護(hù),周期性重建索引,重新編譯存儲過程.

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

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

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

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

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

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

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

(d)合理地算法使用:

根據(jù)上面已提到地sql優(yōu)化技術(shù)和ase tuning手冊中地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 等.

更多信息請查看IT技術(shù)專欄

更多信息請查看數(shù)據(jù)庫
易賢網(wǎng)手機(jī)網(wǎng)站地址:sql server存儲過程的編寫和優(yōu)化措施
由于各方面情況的不斷調(diào)整與變化,易賢網(wǎng)提供的所有考試信息和咨詢回復(fù)僅供參考,敬請考生以權(quán)威部門公布的正式信息和咨詢?yōu)闇?zhǔn)!

2025國考·省考課程試聽報名

  • 報班類型
  • 姓名
  • 手機(jī)號
  • 驗(yàn)證碼
關(guān)于我們 | 聯(lián)系我們 | 人才招聘 | 網(wǎng)站聲明 | 網(wǎng)站幫助 | 非正式的簡要咨詢 | 簡要咨詢須知 | 加入群交流 | 手機(jī)站點(diǎn) | 投訴建議
工業(yè)和信息化部備案號:滇ICP備2023014141號-1 云南省教育廳備案號:云教ICP備0901021 滇公網(wǎng)安備53010202001879號 人力資源服務(wù)許可證:(云)人服證字(2023)第0102001523號
云南網(wǎng)警備案專用圖標(biāo)
聯(lián)系電話:0871-65099533/13759567129 獲取招聘考試信息及咨詢關(guān)注公眾號:hfpxwx
咨詢QQ:526150442(9:00—18:00)版權(quán)所有:易賢網(wǎng)
云南網(wǎng)警報警專用圖標(biāo)