T-SQL Optimization Tips (4) : 遊標

這裡所說的遊標當然是指Transaction-SQL遊標。任何類型的遊標都會降低SQL Server的效能，然而遊標之所以會存在是因為在某些情境中還是會不可避免地用到它，我們應該做的是盡量避免遊標。

如果需要在T-SQL中對記錄集進行逐行操作，可以考慮使用以下方案代替遊標：

• 暫存資料表
• WHILE迴圈
• 派生表
• 關聯子查詢
• CASE
• 多重查詢

上面列舉的替代方案的效能也是不一樣的，在某些時候某些方案甚至可能也會對效能產生不利影響，但是它們都比遊標的效能要好。之所以列舉具備不同效能的替代方案，是因為由於資料結構的不同，很可能並不是很容易就可以使用任意一種替代方案來代替遊標，此時可以考慮其它的替代方案。

如果一定要在T-SQL中使用遊標，那麼至少應當對遊標本身進行最佳化：

1）減少需要處理的記錄集行數：如果遊標處理的對象不是整個原始表中的所有記錄，那麼可以考慮先將資料子集放入到一個暫存資料表中，使遊標只處理暫存資料表中的資料；記錄集的列數也應當盡量減少，以只擷取用戶端需要的資料為標。記錄集越小，佔用的資源就會越少，效能就會越高。

2）如果從某個查詢返回的結果集的行數較少，而同時又需要對該結果集進行逐行操作，此時不要使用伺服器端遊標，而考慮將整個返回的結果集發送到用戶端，由用戶端完成對每行的必要的操作並將更新過的行集返回給伺服器；而如果資料量較大時，應當考慮使用伺服器端鍵集遊標代替用戶端資料指標，效能會由於伺服器端與用戶端網路通訊的減少得到提升。當然，應當在實際負載下嘗試這兩種不同的遊標從而決定哪種遊標的效能更好一些。

3）如果確實需要使用伺服器端遊標，應該盡量使用順向資料指標（FORWARD_ONLY）或是更最佳化的快速只進（FAST_FORWARD）遊標。再退一步，如果無法使用這些選項，那麼按照從快到慢順序排列的應當使用的遊標是：動態資料指標、靜態資料指標和鍵集遊標；

4）盡量避免使用靜態資料指標和鍵集遊標，它們需要在tempdb中建立暫存資料表從而增加伺服器資源開銷；

5）遊標使用tempdb資料庫儲存遊標中的資料，應當將tempdb資料庫定位到其自身的物理裝置上以加快遊標資料的讀取從而提高遊標的效能。

6）遊標的使用可能導致並發效能降低，並可能導致不必要的鎖定或阻塞。可能的話，盡量使用唯讀遊標；如果希望進行更新操作，應當為遊標指定OPTIMISTIC選項以避免，該遊標不鎖定行；避免使用帶有SCROLL_LOCKS選項的遊標，否則並發效能會降低。

 7）使用遊標進行必要的操作之後，僅僅關閉（CLOSE）該遊標是不夠的，關閉之後還應當刪除對該遊標的引用（DEALLOCATE）。刪除遊標引用用來釋放被遊標佔用的SQL Server資源，如果僅僅關閉遊標的話，鎖定被釋放，但遊標佔用的SQL Server資源並未被釋放。

 8）如果可能的話，應當儘快移動到結果集的最後一行以快速載入遊標，這樣可以釋放在遊標建立時的共用鎖定定，解除對SQL Server資源的佔用。

9）如果需要在遊標中進行JOIN操作，鍵集遊標和靜態資料指標一般情況下會比動態資料指標快，因此這種情況下應當盡量使用鍵集遊標和靜態資料指標。

10）如果事務中包含遊標（應當盡量避免這種情況），應當確保需要被遊標修改的行數不能太多，被修改的行會被鎖定直到事務完成或被取消：要修改的行數越多，鎖定的行數就越多，伺服器上發生資源資源爭奪的機率就越高，效能就越低。

11）本地（LOCAL）遊標比全域(GLOBAL)遊標具有更好的效能；但是，如果需要在一個批處理中多次使用同一個遊標或者在多個預存程序中使用同一個遊標，應當考慮使用全域遊標，此時遊標和包含在其中的資料是預先準備好的，從而可以節省一定的處理時間。

12）如果預期要處理的結果集比較大時，可以考慮使用非同步填充遊標：如果SQL Server 查詢最佳化工具估計鍵集驅動或靜態資料指標中返回的行數將超過sp_configure cursor threshold 參數的值，伺服器就會啟動另一個線程來填充工作表。控制權立即返回到應用程式，應用程式開始提取遊標中前面的行，而無須等到整個工作表填充完以後才開始執行首次提取。事實上，這並不會在速度上得到真正的提升，但是可以讓終端使用者得到更好的使用者體驗。

13）如果在使用遊標時發現出錯或者處理提前完成，應當在遍曆整個行集之前儘早跳出迴圈。

DOS & DONTS——
5、避免使用遊標；
[注意：為了引起注意，所有本系列總結的DOS & DONTS都是具有一定前提的，一般的前提是“除非必要”和“除非必要才不”。]