RiCo技術農場

Cursor我總是會避免使用，但最近突然開竅While loop底層可能也是Cursor處理

只是while loop表面上沒有明確宣告Cursor，所以這篇優化Cursor就這樣產生了

 

1.預設cursor

 

create proc usp_defcursor
as
set nocount on
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

 

exec usp_defcursor

 

 

2.優化cursor

create proc usp_opticursor
as
set nocount on
DECLARE @ProdCost money
        DECLARE @ProdCostTotal money
        SET @ProdCost = 0
        SET @ProdCostTotal = 0
        DECLARE MyCursor CURSOR
        LOCAL STATIC FORWARD_ONLY READ_ONLY
        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

 

exec usp_opticursor

 

使用FORWARD_ONLY READ_ONLY選項作業快了約10秒，同時CPU所花費時間也少了快13秒

如有使用cursor記得不要忘記該選項。

 

 

 

參考

[SQL SERVER][Performance]盡量避免使用Cursor