SQL Server 最佳化預存程序的七種方法

最佳化預存程序有很多種方法，下面介紹最常用的7種。

1.使用SET NOCOUNT ON選項

我們使用SELECT語句時，除了返回對應的結果集外，還會返回相應的影響行數。使用SET NOCOUNT ON後，除了資料集就不會返回額外的資訊了，減小網路流量。

2.使用確定的Schema

在使用表，預存程序，函數等等時，最好加上確定的Schema。這樣可以使SQL Server直接找到對應目標，避免去計畫快取中搜尋。而且搜尋會導致編譯鎖定，最終影響效能。比如select * from dbo.TestTable比select * from TestTable要好。from TestTable會在當前Schema下搜尋，如果沒有，再去dbo下面搜尋，影響效能。而且如果你的表是csdn.TestTable的話，那麼select * from TestTable會直接報找不到表的錯誤。所以寫上具體的Schema也是一個好習慣。

3.自訂預存程序不要以sp_開頭

因為以sp_開頭的預存程序預設為系統預存程序，所以首先會去master庫中找，然後在當前資料庫找。建議使用USP_或者其他標識開頭。

4.使用sp_executesql替代exec

原因在Inside Microsoft SQL Server 2005 T-SQL Programming書中的第四章Dynamic SQL裡面有具體描述。這裡只是簡單說明一下：sp_executesql可以使用參數化，從而可以重用執行計畫。exec就是純拼SQL語句。

5.少使用遊標

可以參考Inside Microsoft SQL Server 2005 T-SQL Programming書中的第三章Cursors裡面有具體描述。總體來說，SQL是個集合語言，對於集合運算具有較高的效能，而Cursors是過程運算。比如對一個100萬行的資料進行查詢，遊標需要讀表100萬次，而不使用遊標只需要少量幾次讀取。

6.事務越短越好

SQL Server支援並行作業。如果事務過多過長，或是隔離等級過高，都會造成並行作業的阻塞，死結。此時現象是查詢極慢，同時cup佔用率極低。

7.使用try-catch來處理錯誤異常

SQL Server 2005及以上版本提供對try-catch的支援，文法為：

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

一般情況可以將try-catch同事務結合在一起使用。

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