RBAR：Row By Agonizing Row#

T-SQL 的設計哲學是 集合式（set-based）操作——一次處理整個資料集。但人類習慣一筆一筆思考，於是 cursor、WHILE loop 等 逐列處理 的程式碼大量出現在生產環境中。

Jeff Moden 把這種反模式取了一個傳神的綽號：RBAR（Row By Agonizing Row）——用 cursor / WHILE 在 SQL Server 處理每一列，幾乎都比改寫成 set-based 慢一個數量級以上。

Oracle 對 cursor 有不同的最佳化策略，但在 SQL Server 中，cursor 通常是效能殺手。

但 cursor 也並非完全沒理由存在——某些管理工作（資料維護、跨表批次處理）仍可能需要逐列。本章重點在：萬不得已要用 cursor 時，怎麼用對的方式。

Cursor 的基本生命週期#

DECLARE MyCursor CURSOR FAST_FORWARD FOR
    SELECT col FROM dbo.Tbl WHERE ...;
OPEN MyCursor;
FETCH NEXT FROM MyCursor INTO @var;
WHILE @@FETCH_STATUS = 0
BEGIN
    -- ... per-row work ...
    FETCH NEXT FROM MyCursor INTO @var;
END;
CLOSE MyCursor;
DEALLOCATE MyCursor;

完整流程五步：

  1. DECLARE（宣告 SELECT 與特性）
  2. OPEN（建立資料集）
  3. FETCH（取一列；可選 UPDATE）
  4. 反覆 FETCH
  5. CLOSE + DEALLOCATE（釋放資源）

Figure 18-1: Query performance metrics for the cursor

Cursor 的三大特性軸#

cursor 的成本主要由三個維度決定：

  • Location：client-side / server-side
  • Concurrency：read-only / optimistic / scroll locks
  • Type：forward-only / static / keyset / dynamic / FAST_FORWARD

Location：Client vs. Server#

Client-side cursor#

  • 在客戶端維護元資料
  • 透過 OLE DB / ODBC 等資料層
  • 只支援 forward-only 與 static
  • 較高的可攜性（OS / DB 跨平台）
  • 但 客戶端資源壓力大——對 web server 等高並發環境會拖垮

Server-side cursor#

  • 在 SQL Server 上建立與管理
  • T-SQL cursor 全部是 server-side
  • 支援所有 cursor type
  • 同一連線可有多個 active cursor

本章後續討論皆以 server-side cursor 為主——這也是 T-SQL 程式碼裡看到的型態。

Concurrency：併發行為#

模型可更新鎖行為
Read-Only不可fetch 後立即釋放 S 鎖
Optimistic可不持鎖；更新時用 ROWVERSION 或值比對
Scroll Locks可更新鎖（U lock）保留至 fetch 下一列或關閉

Read-Only#

最快、最不阻塞——首選。

DECLARE MyCursor CURSOR READ_ONLY FOR ...;

Optimistic#

可更新 + 鎖少。但有兩種 optimistic 方式：

  • ROWVERSION 比對（更高效）
  • 值比對（更貴）

如果你的表會被 cursor 更新，強烈建議加上 rowversion 欄位——避免 SQL Server 用「全欄位值比對」這種成本高的方式做 concurrency check。

Scroll Locks#

直接持 U 鎖到下一列。最強一致性、最差併發——只在「資料完全不能被別人改」時用。

Type：cursor 的行為類型#

Forward-Only / FAST_FORWARD#

只能往前 FETCH，不能回頭、不能跳。最輕量。

DECLARE MyCursor CURSOR FAST_FORWARD FOR ...;

FAST_FORWARD 是 T-SQL cursor 中 最快的選項——綽號「fire hose」。但仍比 set-based 慢。

Static#

OPEN 時把資料 快照到 tempdb，整個 cursor 期間資料不變。支援所有 FETCH 操作（FIRST / LAST / ABSOLUTE / RELATIVE）。但 讀取後底層資料變動不會反映。

Keyset-Driven#

只把 unique key 存進 tempdb，每次 FETCH 才回 base table 取資料。

  • INSERT 進來的列不在 cursor 中
  • DELETE 的列在 FETCH 時報錯
  • UPDATE 過的列會看到新值

Dynamic#

與底層表完全同步。每次 FETCH 都直接讀 base table。

Dynamic cursor 是 最慢的選項——鎖最多、持鎖最久、所有變動都即時反映。除非真的需要「即時資料」，否則別用。

WHILE Loop#

技術上不算 cursor，但行為等同 RBAR。書中範例：

DECLARE @LoopTable TABLE (LoopID INT IDENTITY, SalesOrderDetailID INT);
INSERT INTO @LoopTable (SalesOrderDetailID)
SELECT SalesOrderDetailID FROM Sales.SalesOrderDetail WHERE OrderQty > 23
ORDER BY SalesOrderDetailID DESC;

DECLARE @MaxRow INT, @Count INT = 1, @ID INT;
SELECT @MaxRow = MAX(LoopID) FROM @LoopTable;

WHILE @Count <= @MaxRow
BEGIN
    SELECT @ID = SalesOrderDetailID FROM @LoopTable WHERE LoopID = @Count;
    -- per-row work
    SET @Count += 1;
END;

在某些情境下 WHILE loop 比 cursor 還快——因為 cursor 的 metadata 開銷可省掉。但仍 遠不如 set-based。

成本比較重點#

依 Location#

  • Client-side：scale 較好但客戶端壓力高，cursor 類型受限
  • Server-side：類型完整、靠近資料、但占伺服器資源

依 Concurrency#

模型鎖開銷併發可寫
Read-Only最低最高✗
Optimistic低高✓
Scroll Locks高低✓

Read-Only / Optimistic 都常用 WITH (NOLOCK) 進一步降鎖開銷——但 NOLOCK 會讀到 dirty data、漏列、重複讀。本書不推薦——應改用 RCSI。

依 Type#

從快到慢大致：

FAST_FORWARD > STATIC > KEYSET > DYNAMIC

Default Result Set vs. Cursor#

多數情境下不需要 cursor，直接用 default result set（普通 SELECT）讓客戶端逐列讀，可能比明確宣告 cursor 還快——SQL Server 不必建 cursor metadata。

但 default result set：

  • 整個結果集 在 transaction 期間佔資源
  • 客戶端必須及時處理
  • 不能跨 statement 暫停

Figure 18-2: Locks held by the default result set

把 Cursor 改寫成 Set-Based#

幾個常見模式：

用 UPDATE … FROM 取代逐列 UPDATE#

-- 壞：cursor 逐列 UPDATE
DECLARE c CURSOR FOR SELECT id FROM Tbl1 WHERE flag = 1;
OPEN c;
FETCH NEXT FROM c INTO @id;
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE Tbl2 SET total = total + 1 WHERE foreign_id = @id;
    FETCH NEXT FROM c INTO @id;
END;

-- 好：set-based
UPDATE t2
SET total = total + 1
FROM Tbl2 t2
JOIN Tbl1 t1 ON t1.id = t2.foreign_id
WHERE t1.flag = 1;

用 CTE / window function 取代「比對前後列」#

許多需要 cursor 的場景其實是 LAG() / LEAD() / ROW_NUMBER() 等 window function 能解的問題。

用 MERGE 取代「if exists then update else insert」#

跨表的 upsert 場景，與其用 cursor 逐列判斷，不如直接 MERGE 一次解決——但要注意 MERGE 也有自己的陷阱（race condition、奇怪的 trigger 行為），用前先確認版本與情境。

真的需要 Cursor 的場景#

  • 管理性任務：對每個資料庫做 backup、對每個表 rebuild index、對每個 schema 套權限
  • 跨資料庫呼叫：每個目標 DB 上跑相同的程序
  • 逐列觸發外部處理：例如針對每筆訂單呼叫一個 stored procedure（雖然能改寫但有時不值得）

這些場景的共通點是 每列都觸發昂貴外部動作，逐列處理本來就無可避免。重點變成：用最輕量的 cursor 形式——FAST_FORWARD + READ_ONLY。

本章定調#

  • T-SQL 是為 set-based 而生；cursor 與 WHILE loop 都是 RBAR
  • 必須用 cursor 時：選 server-side + READ_ONLY + FAST_FORWARD（除非有理由用其他組合）
  • 加 ROWVERSION 欄位讓 OPTIMISTIC cursor 跑得起來
  • DYNAMIC cursor 是最後手段
  • 多數 cursor 程式都能改寫成 UPDATE ... FROM / MERGE / window function——花時間改寫值得

下一章將進入 記憶體最佳化 OLTP——SQL Server 為極高並發、低延遲設計的 in-memory table 與 native compiled procedure。

Backward 死結成因與解法 記憶體最佳化 OLTP Forward