上篇提到，一段看起來沒有問題的 Cursor，因為同時「從同一張表讀資料，又寫回同一張表」，最後讓資料量一路膨脹，效能也跟著快速惡化；理解原因之後，接下來要探討的是：如果接手這樣的問題，該怎麼調整或修改？

可以怎麼調整？

拆出獨立的「來源表」，把自我參照分開

修改幅度最小的做法；把母體先複製到另一張暫存表，Cursor 從來源表讀、寫入目標表，兩者不會混在一起：

 1-- 先把「會員母體」獨立出來
 2SELECT DISTINCT member_id, member_name, member_level
 3INTO #member_source
 4FROM #member_base;
 5
 6-- 清空目標表（如果需要的話）
 7TRUNCATE TABLE #member_base;
 8
 9DECLARE month_cursor CURSOR FOR SELECT month_id FROM dim_month;
10OPEN month_cursor;
11FETCH NEXT FROM month_cursor INTO @month_id;
12
13WHILE @@FETCH_STATUS = 0
14BEGIN
15    INSERT INTO #member_base (member_id, member_name, member_level, month_id, amount)
16    SELECT member_id, member_name, member_level, @month_id, 0
17    FROM #member_source;   -- 來源固定，不會膨脹
18
19    FETCH NEXT FROM month_cursor INTO @month_id;
20END
21
22CLOSE month_cursor;
23DEALLOCATE month_cursor;
24DROP TABLE #member_source;
25
26這個改法只處理了「資料爆炸」的部分，Cursor 本身的效能成本還在；不過如果業務邏輯比較複雜、確實需要逐筆處理，這算是相對安全的最小改動。

用 CROSS JOIN 一次處理（通常會比較推薦）

仔細看這個需求，就會發現他的本質其實就是「會員集合 × 月份集合」，如果能用集合運算的話，通常就不太需要用 Cursor：

1-- 一次批量 INSERT，產生所有 (會員, 月份) 組合
2INSERT INTO #result (member_id, member_name, member_level, month_id, amount)
3SELECT 
4    m.member_id, m.member_name, m.member_level,
5    d.month_id,
6    0
7FROM #member_source m
8CROSS JOIN dim_month d;

一句 SQL 就能取代整段 Cursor，而且：

  • 沒有迴圈、沒有自我參照
  • Query optimizer 可以做平行處理
  • Transaction log 只寫一次
  • 邏輯比較清楚，後面看到的人也比較容易理解

執行時間可能會從幾分鐘變幾秒。

CTE 或派生表，適合需要做一些前置運算的情況

如果情境再複雜一點，例如「每個會員只要產生其註冊月份之後的紀錄」，可以用 CTE 來寫：

1WITH eligible_combinations AS (
2    SELECT m.member_id, m.member_name, m.member_level, d.month_id
3    FROM #member_source m
4    CROSS JOIN dim_month d
5    WHERE d.month_id >= m.register_month
6)
7INSERT INTO #result (member_id, member_name, member_level, month_id, amount)
8SELECT member_id, member_name, member_level, month_id, 0
9FROM eligible_combinations;

一樣是集合運算，一樣不需要用到 Cursor。

怎麼判斷自己的 SQL 有沒有遇到類似狀況？

可以用幾個問題來檢查：

  • 有沒有 INSERT INTO X ... SELECT ... FROM X 出現在迴圈裡？這通常會是滿明顯的訊號。
  • 暫存表的資料量會不會在迴圈中持續變大？在 Cursor 開頭和結尾各加一個 SELECT COUNT(*) 印出來看就會知道。
  • 同樣的邏輯能不能用 JOIN 或 CROSS JOIN 一句寫完？滿多 Cursor 其實都可以這樣改寫。
  • 這段 SQL 是不是「以前資料少時跑得很快、最近卻變慢很多」？指數成長的特徵就是「資料量到了某個量之後會明顯變慢」。

為什麼舊程式碼常常會這樣寫？

這種寫法在早期的 stored procedure 中還滿常見的，背後的脈絡可以理解為：

  • 思維習慣：習慣程式語言的開發者，比較熟悉「for 迴圈處理每筆資料」的思考方式，因此寫成 Cursor 比較自然。
  • 需求逐步加上去：一開始可能只是單純的查詢，後來陸續加上「也要展開月份」「也要展開分店」，Cursor 就被加進來了。
  • 資料量還沒長大時看不出來：開發環境跑 100 筆很快，上 production 跑個 10 萬筆狀況就會比較明顯。
  • 暫存表的用途模糊：開發者可能沒注意到自己邊讀邊寫同一張表。

這些都是滿正常的開發過程，不是誰的問題，而是隨著時間累積下來的技術債。

T-SQL 效能調整有一個還算實用的方向：能用集合運算就盡量用集合運算，能用一句 SQL 完成就不用 Cursor。

如果接手一段舊程式碼覺得效能不太理想，可以先檢查三件事：

  • 有沒有「自我參照的 INSERT」造成資料變大？
  • 有沒有用 Cursor 在做其實可以用集合運算解決的事？
  • 迴圈內有沒有重複執行的 DISTINCT 或全表掃描？

把這三個地方處理掉，效能通常會有不錯的改善。

T-SQL 跟一般程式語言的思考方式不太一樣，比較像是在「描述資料」；寫的時候可以多問自己一句：「能不能用一句 SQL 就得到想要的結果？」這個思考習慣會對寫出效能比較好的 T-SQL 滿有幫助的。