[SQL SERVER][Performance]盡量避免使用Cursor
今天朋友通報前端執行某個在SQL2000的SP後，常常發生逾時而且Server CPU使用率暴增，
我在收到SP內容後，發現裡面有段邏輯是使用Cursor執行row by row(一筆一筆)的操作，
心中一想應該就是這個光了，這裡我大概模擬一下狀況。
我用SQL2008模擬測試(SQL2000不知道丟那去了….XD)
使用Cursor
--定義游標變數
DECLARE @ProdCost money
DECLARE @ProdCostTotal money
SET @ProdCost = 0
SET @ProdCostTotal = 0
DECLARE MyCursor CURSOR FOR
SELECT ActualCost*Quantity
FROM Production.TransactionHistory
--開啟游標
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @ProdCost
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ProdCostTotal = @ProdCostTotal + @ProdCost
FETCH NEXT FROM MyCursor INTO @ProdCost
END
CLOSE MyCursor
DEALLOCATE MyCursor
SELECT @ProdCostTotal ProdCostTotal
執行期間:3465
用戶端統計資料(執行3次平均)
總執行時間:3088.667、伺服器收到的TDS封包:1083、SELECT傳回資料列:113446
不使用Cursor
DECLARE @ProdCostTotal money
SELECT @ProdCostTotal = sum(ActualCost*Quantity)
FROM Production.TransactionHistory
SELECT @ProdCostTotal ProdCostTotal
執行期間:57
用戶端統計資料(執行3次平均)
總執行時間:188、伺服器收到的TDS封包:1、SELECT傳回資料列:2
結論:
可以看到使用Cursor會造成耗時的工作、伺服器收到大量TDS封包(耗掉伺服器大量系統資源以及網路資源)，
而且Lock也伴隨而來，個人覺得使用Cursor是真的不得已才使用(當使用時，也請一定要減少傳回的資料列)，
否則應該檢視所有使用Cursor的程式碼看看是否可以用TSQL或其他方法取代，
例如:
先將所需要處理的資料塞到Temp Table，然後在開啟Cursor並針對Temp Table處理(不要針對原本Table);
Server Side Cursor也請使用FORWARD_ONLY/READ_ONLY選項藉以優化效能 ;
Cursor類型切勿選用Static和Keyset，因為這些類型的Cursor會將結果放在Temp Table，
如果資料過大，就很容易讓TEMPDB暴增，進而影響效能，
FAST_FORWARD類型會有較佳效能(節省網路往返次數)。
總之，當你在使用Cursor一定要小心謹慎。
參考