需求背景
- 三表：`web_inputCj_cjb` (學生-課程)、`web_inputCj_xsksb` (課程-考試-比例)、`web_inputCj_xscjb` (學生-考試-成績)。
- 依考試比例 (`cjbl`，0-100) 計算加權總分，寫回 `web_inputCj_cjb.cj`。
原始游標方案 (效能差、鎖久、難維護)
DECLARE c1 CURSOR FOR
SELECT zzcj.cj, zzcj.xh FROM ...; -- 複雜子查詢
OPEN c1;
FETCH NEXT FROM c1 INTO @cj, @xh;
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE web_inputCj_cjb SET cj=@cj WHERE xh=@xh AND kcId=@kcId;
FETCH NEXT FROM c1 INTO @cj, @xh;
END
CLOSE c1; DEALLOCATE c1;
優化方案：單一陳述式集合式更新 (CTE + JOIN)
;WITH ExamWeights AS (
-- 1. 取得該課程所有考試及比例
SELECT ksid, cjbl FROM web_inputCj_xsksb WHERE kcId = @kcid
),
StudentScores AS (
-- 2. 學生各考試成績
SELECT xscjb.xh, xscjb.ksid, ISNULL(xscjb.cj, 0) AS cj
FROM web_inputCj_xscjb xscjb
JOIN ExamWeights ew ON xscjb.ksid = ew.ksid
),
WeightedTotal AS (
-- 3. 計算加權總分 (四捨五入)
SELECT
ss.xh,
ROUND(SUM(ss.cj * ew.cjbl * 0.01), 0) AS final_score
FROM StudentScores ss
JOIN ExamWeights ew ON ss.ksid = ew.ksid
GROUP BY ss.xh
)
-- 4. 集合式更新目標表
UPDATE cjb
SET cjb.cj = wt.final_score
FROM web_inputCj_cjb cjb
JOIN WeightedTotal wt ON cjb.xh = wt.xh
WHERE cjb.kcId = @kcid;
關鍵優化點
| 維度 | 游標方案 | 集合式方案 |
|---|
| 執行計畫 | N 次單獨 UPDATE (N = 學生數) | 單一掃描 + Hash/Aggregate + 單次 UPDATE |
| 鎖持有時間 | 長 (逐列提交) | 短 (單一交易) |
| 記錄檔產生 | 大量小交易 | 單一大交易 (可配合分批) |
| 平行處理 | 不可 | 優化器可並行化聚合/聯結 |
| 可讀性/維護性 | 低 (過程式) | 高 (宣告式) |
大量資料分批更新模式 (避免長鎖/記錄檔暴增)
DECLARE @batchSize INT = 5000, @rows INT = 1;
WHILE @rows > 0
BEGIN
;WITH wt AS ( ...同上... )
UPDATE TOP (@batchSize) cjb
SET cjb.cj = wt.final_score
FROM web_inputCj_cjb cjb
JOIN wt ON cjb.xh = wt.xh
WHERE cjb.kcId = @kcid AND cjb.cj <> wt.final_score; -- 僅更新有變化列
SET @rows = @@ROWCOUNT;
WAITFOR DELAY '00:00:02';
END
進階：MERGE 語法 (SQL 2008+，同步 INSERT/UPDATE/DELETE)
;WITH wt AS ( ...同上... )
MERGE web_inputCj_cjb AS target
USING wt AS source ON target.xh = source.xh AND target.kcId = @kcid
WHEN MATCHED AND target.cj <> source.final_score
THEN UPDATE SET cj = source.final_score
WHEN NOT MATCHED BY TARGET
THEN INSERT (kcId, xh, cj) VALUES (@kcid, xh, final_score);
