最佳化遊標效能

最好的改進游標效能的技術就是：能避免時就避免使用遊標。

 

——摘自《Transact-SQL權威指南》 Ken Henderson[著]

    最好的改進游標效能的技術就是：能避免時就避免使用遊標。SQL Server是關聯式資料庫，其處理資料集比處理單行好得多，單獨行的訪問根本不適合關係DBMS。若有時無法避免使用遊標，則可以用如下技巧來最佳化遊標的效能。
(1). 除非必要否則不要使用static/insensitive遊標。開啟static遊標會造成所有的行都被拷貝到暫存資料表。這正是為什麼它對變化不敏感的原因——它實際上是指向臨時資料庫表中的一個備份。很自然，結果集越大，聲明其上的static遊標就會引起越多的臨時資料庫的資源爭奪問題。
(2). 除非必要否則不要使用keyset遊標。和static遊標一樣，開啟keyset遊標會建立暫存資料表。雖然這個表只包括基本表的一個關鍵字列(除非不存在唯一關鍵字)，但是當處理大結果集時還是會相當大的。
(3). 當處理單向的唯讀結果集時，使用fast_forward代替forward_only。使用fast_forward定義一個forward_only，則read_only遊標具有一定的內部效能最佳化。
(4). 使用read_only關鍵字定義唯讀遊標。這樣可以防止意外的修改，並且讓伺服器瞭解遊標移動時不會修改行。
(5). 小心交易處理中通過遊標進行的大量行修改。根據交易隔離等級，這些行在事務完成或復原前會保持鎖定，這可能造成伺服器上的資源爭奪。
(6). 小心動態游標的修改，尤其是建在非唯一叢集索引鍵的表上的遊標，因為他們會造成“Halloween”問題——對同一行或同一行的重複的錯誤的修改。因為SQL Server在內部會把某行的關鍵字修改成一個已經存在的值，並強迫伺服器追加下標，使它以後可以再結果集中移動。當從結果集的剩餘項中存取時，又會遇到那一行，然後程式會重複，結果造成死迴圈。
(7). 對於大結果集要考慮使用非同步遊標，儘可能地把控制權交給調用者。當返回相當大的結果集到可移動的表格時，非同步遊標特別有用，因為它們允許應用程式幾乎馬上就可以顯示行。

Halloween:

CREATE Table #T(

    k1 int identity(1,1),

    c1 int null

);

CREATE CLUSTERED INDEX C1 ON #T(C1);

INSERT INTO #T(C1) VALUES(8)

INSERT INTO #T(C1) VALUES(6)

INSERT INTO #T(C1) VALUES(7)

INSERT INTO #T(C1) VALUES(5)

INSERT INTO #T(C1) VALUES(3)

INSERT INTO #T(C1) VALUES(0)

INSERT INTO #T(C1) VALUES(9)

DECLARE C CURSOR DYNAMIC

    FOR SELECT K1,C1 FROM #T;

OPEN C

FETCH C

WHILE(@@FETCH_Status=0)

BEGIN

    UPDATE #T SET C1=C1+1

        WHERE CURRENT OF C;

    FETCH C;

END

CLOSE C;

DEALLOCATE C;

DROP Table #T;

GO