背景

水位監測系統的主查詢開始出現「執行逾時到期。在作業完成之前超過逾時等待的時間，或是伺服器未回應。」，這是應用層的 CommandTimeout（預設 30 秒），一旦觸發就連帶觸發 LINE 通知錯誤，警報不斷。

初始情況

系統概況

  • 共 120 個監測站點（stt_no）
  • 每 10 分鐘向 WaterLevelGaugeHistory 匯入一次資料，觀察時已累積逾 500 萬筆
  • 需要查詢每個站點最近 24 小時的最新一筆

原始查詢

執行時間約 1 分鐘以上：

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT DISTINCT 
    WL.[ID], WL.[stt_name], /* 其他欄位 ... */
    WLH.[measure_time], WLH.[val], WLH.[Status], WLH.cstatus, WLH.[Epower], WLH.nodata
FROM [dbo].[WaterLevelGauge] WL
LEFT JOIN [dbo].[WaterLevelGauge_detailed_View] WLD 
    ON WL.[stt_name] = WLD.[MonitoringStationNumber]
LEFT JOIN (
    SELECT [ID], [stt_no], [dev_id], [measure_time], [val], [Status], [cstatus], [nodata], [power] AS Epower, 
    ROW_NUMBER() OVER (PARTITION BY [stt_no] ORDER BY [measure_time] DESC) AS row_num 
    FROM [dbo].[WaterLevelGaugeHistory] 
    WHERE [dev_id] IS NOT NULL
) WLH ON WL.[stt_no] = WLH.[stt_no] AND WLH.row_num = 1
WHERE WL.[memo2] <> '停用' OR WL.[memo2] IS NULL;

瓶頸在子查詢：它對整張 500 萬筆的 WaterLevelGaugeHistory 跑 ROW_NUMBER()，沒有時間範圍限制，也沒有索引可用。

優化過程

步驟 1：重構子查詢並加時間過濾

把時間條件推進子查詢，讓 ROW_NUMBER() 只看最近 24 小時的資料。120 個站點、每 10 分鐘一筆，24 小時最多 144 × 120 = 17,280 筆，比 500 萬少了幾個數量級：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 子查詢：只取每個站點最近 24 小時內的最新一筆
SELECT
    [ID], [stt_no], [dev_id], [measure_time], [val], [Status], 
    [cstatus], [nodata], [power] AS Epower
FROM (
    SELECT 
        [ID], [stt_no], [dev_id], [measure_time], [val], [Status], 
        [cstatus], [nodata], [power],
        ROW_NUMBER() OVER (PARTITION BY [stt_no] ORDER BY [measure_time] DESC) AS row_num
    FROM [dbo].[WaterLevelGaugeHistory]
    WHERE [dev_id] IS NOT NULL
        AND [measure_time] >= DATEADD(HOUR, -24, GETDATE())
) ranked
WHERE row_num = 1

同時審視原始查詢對 WaterLevelGauge_detailed_View 的 LEFT JOIN：那個 View 的欄位在最終 SELECT 清單中並未實際使用，屬於多餘的連接，直接移除。

完整優化後的外層查詢：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT DISTINCT 
    WL.[ID], WL.[stt_name], /* 其他欄位 ... */
    WLH.[measure_time], WLH.[val], WLH.[Status], WLH.cstatus, WLH.[Epower], WLH.nodata
FROM [dbo].[WaterLevelGauge] WL
LEFT JOIN (
    SELECT
        [ID], [stt_no], [dev_id], [measure_time], [val], [Status], 
        [cstatus], [nodata], [power] AS Epower
    FROM (
        SELECT 
            [ID], [stt_no], [dev_id], [measure_time], [val], [Status], 
            [cstatus], [nodata], [power],
            ROW_NUMBER() OVER (PARTITION BY [stt_no] ORDER BY [measure_time] DESC) AS row_num
        FROM [dbo].[WaterLevelGaugeHistory]
        WHERE [dev_id] IS NOT NULL
            AND [measure_time] >= DATEADD(HOUR, -24, GETDATE())
    ) ranked
    WHERE row_num = 1
) WLH ON WL.[stt_no] = WLH.[stt_no]
WHERE WL.[memo2] <> '停用' OR WL.[memo2] IS NULL;

步驟 2：建覆蓋索引

查詢的存取模式是「先用 stt_no 分組、再按 measure_time 降序取最新一筆」，索引鍵欄位照這個順序設：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- WaterLevelGaugeHistory：鍵欄位涵蓋過濾與排序，INCLUDE 涵蓋常用輸出欄位
CREATE NONCLUSTERED INDEX IX_WaterLevelGaugeHistory_stt_no_measure_time
ON [dbo].[WaterLevelGaugeHistory] ([stt_no], [measure_time] DESC)
INCLUDE ([val], [Status], [cstatus])
WITH (SORT_IN_TEMPDB = ON);

-- WaterLevelGauge：JOIN 條件用到 stt_no
CREATE NONCLUSTERED INDEX IX_WaterLevelGauge_stt_name
ON [dbo].[WaterLevelGauge] ([stt_name])
WITH (SORT_IN_TEMPDB = ON);

-- 建完索引後更新統計資訊，讓查詢最佳化工具拿到最新的分布資訊
UPDATE STATISTICS [dbo].[WaterLevelGaugeHistory] WITH FULLSCAN;
UPDATE STATISTICS [dbo].[WaterLevelGauge] WITH FULLSCAN;

INCLUDE 欄位沒有納入 dev_id、nodata、power，這是刻意的取捨：dev_id 雖出現在 WHERE（IS NOT NULL），但幾乎所有資料列都非 NULL，過濾效益有限；nodata、power 欄位相對較大。把這三個欄位加進 INCLUDE 會增加索引體積與維護成本。實際上因為 ROW_NUMBER 把結果集收窄到最多 120 筆，回表次數本來就可接受，不加 INCLUDE 是合理的。

優化結果

用 SET STATISTICS TIME ON 量測：

  • 原始查詢：~60 秒
  • 優化後：~1 秒
  • 改善幅度：約 98%（60→1 秒的實測比率）

數字背後的原理：子查詢從掃描 500 萬筆降到最多 17,280 筆，再加上覆蓋索引讓過濾階段免去大量回表，這兩個改動相乘才跑出這個數字。

為什麼效果這麼明顯

時間過濾推進子查詢是關鍵。原本 ROW_NUMBER() 要看完全部 500 萬筆才知道哪一筆是「最新的」；加了 24 小時限制之後，每個站點最多 144 筆，全部站點合計不超過 1.7 萬筆，計算量直接砍了幾個數量級。

索引鍵欄位順序要對。(stt_no, measure_time DESC) 讓 SQL Server 能針對每個 stt_no 範圍直接從最新的時間往前讀，不用排序。如果順序對調成 (measure_time, stt_no) 效果會差很多。

移除多餘 JOIN（WaterLevelGauge_detailed_View）消除了一個 View 的額外展開，這個 View 本身可能含有複雜的子查詢或多層 JOIN。

資料分布剛好配合：120 個站點、固定 10 分鐘頻率，資料非常均勻，PARTITION BY stt_no 的效率最高，不會有某個 stt_no 累積異常多筆的偏態問題。

後續建議

定期維護：索引片段化程度超過 30% 就重建（ALTER INDEX ... REBUILD），統計資訊建議每週更新一次或在大量批次匯入後手動觸發。

資料保留策略：目前靠時間過濾迴避全表掃描，但 500 萬筆仍會讓索引維護成本持續增長。考慮只在主表保留近三個月，舊資料歸檔到歷史表。

查詢存放區（Query Store）監控：開啟後可以追蹤執行計畫的漂移，在計畫被換掉導致效能回退時提前發現。

分區表：如果資料量繼續增長到數千萬筆，按月分區配合 partition elimination 能讓時間過濾的效果更直接；但這是資料量真正成長到那個規模才需要考慮的事，目前不必急。