SQL Server 優(yōu)化存儲(chǔ)過(guò)程的七種方法

優(yōu)化存儲(chǔ)過(guò)程有很多種方法，下面介紹最常用的7種。

1.使用SET NOCOUNT ON選項(xiàng)

我們使用SELECT語(yǔ)句時(shí)，除了返回對(duì)應(yīng)的結(jié)果集外，還會(huì)返回相應(yīng)的影響行數(shù)。使用SET NOCOUNT ON后，除了數(shù)據(jù)集就不會(huì)返回額外的信息了，減小網(wǎng)絡(luò)流量。

2.使用確定的Schema

在使用表，存儲(chǔ)過(guò)程，函數(shù)等等時(shí)，最好加上確定的Schema。這樣可以使SQL Server直接找到對(duì)應(yīng)目標(biāo)，避免去計(jì)劃緩存中搜索。而且搜索會(huì)導(dǎo)致編譯鎖定，最終影響性能。比如select * from dbo.TestTable比select * from TestTable要好。from TestTable會(huì)在當(dāng)前Schema下搜索，如果沒(méi)有，再去dbo下面搜索，影響性能。而且如果你的表是csdn.TestTable的話，那么select * from TestTable會(huì)直接報(bào)找不到表的錯(cuò)誤。所以寫上具體的Schema也是一個(gè)好習(xí)慣。

3.自定義存儲(chǔ)過(guò)程不要以sp_開(kāi)頭

因?yàn)橐詓p_開(kāi)頭的存儲(chǔ)過(guò)程默認(rèn)為系統(tǒng)存儲(chǔ)過(guò)程，所以首先會(huì)去master庫(kù)中找，然后在當(dāng)前數(shù)據(jù)庫(kù)找。建議使用USP_或者其他標(biāo)識(shí)開(kāi)頭。

4.使用sp_executesql替代exec

原因在Inside Microsoft SQL Server 2005 T-SQL Programming書中的第四章Dynamic SQL里面有具體描述。這里只是簡(jiǎn)單說(shuō)明一下：sp_executesql可以使用參數(shù)化，從而可以重用執(zhí)行計(jì)劃。exec就是純拼SQL語(yǔ)句。

5.少使用游標(biāo)

可以參考Inside Microsoft SQL Server 2005 T-SQL Programming書中的第三章Cursors里面有具體描述?？傮w來(lái)說(shuō)，SQL是個(gè)集合語(yǔ)言，對(duì)于集合運(yùn)算具有較高的性能，而Cursors是過(guò)程運(yùn)算。比如對(duì)一個(gè)100萬(wàn)行的數(shù)據(jù)進(jìn)行查詢，游標(biāo)需要讀表100萬(wàn)次，而不使用游標(biāo)只需要少量幾次讀取。

6.事務(wù)越短越好

SQL Server支持并發(fā)操作。如果事務(wù)過(guò)多過(guò)長(zhǎng)，或是隔離級(jí)別過(guò)高，都會(huì)造成并發(fā)操作的阻塞，死鎖。此時(shí)現(xiàn)象是查詢極慢，同時(shí)cup占用率極低。

7.使用try-catch來(lái)處理錯(cuò)誤異常

SQL Server 2005及以上版本提供對(duì)try-catch的支持，語(yǔ)法為：

begin try??
????? ----your code?
end try?
begin catch?
?????? --error dispose?
end catch

一般情況可以將try-catch同事務(wù)結(jié)合在一起使用。

begin try?
??? begin tran?
??????? --select?
??????? --update?
??????? --delete?
??????? --…………?
??? commit?
end try?
begin catch?
??? --if error?
??? rollback?
end catch