SQL Server 2000 跨伺服器大量資料查詢實戰經驗總結：效能優化六大原則

專案背景

撰寫跨伺服器、跨版本（SQL 6.5/2000）、大量資料、複雜業務邏輯的預存程序，全後台處理。過程中踩坑眾多，經同事協助調優後程序可跑通，但效能仍待改善。整理六大核心經驗原則。

六大核心原則

1. 建立連結伺服器集中管理，避免硬編碼

-- 一次建立，處處引用
EXEC sp_addlinkedserver 
  @server = 'LINK_PROD', 
  @srvproduct = 'SQL Server';
EXEC sp_addlinkedsrvlogin 
  @rmtsrvname = 'LINK_PROD', 
  @useself = 'FALSE', 
  @locallogin = NULL, 
  @rmtuser = 'remote_user', 
  @rmtpassword = 'password';

• 統一連線字串、認證、選項設定。
• 遷移/變更目標伺服器時，只需修改連結伺服器定義，程式碼無需動刀。
• 便於權限審計與安全性管理。

2. 嚴格限制跨伺服器傳輸資料量

• WHERE 條件下推：使用 OPENQUERY 或遠端預存程序，在遠端完成篩選、彙總，只回傳結果集。
• 欄位投影：SELECT col1, col2 而非 SELECT *，減少網路頻寬與序列化開銷。
• 分頁/分批：大量資料分批處理，避免單次交易過大導致逾時或日誌暴漲。

3. 本地暫存表替代跨伺服器 JOIN

-- 錯誤做法：直接跨伺服器 JOIN
SELECT * FROM LocalTable L
JOIN LinkedServer.DB.dbo.RemoteTable R ON L.id = R.id;

-- 正確做法：先抓遠端資料到本地暫存表
SELECT id, col1, col2 INTO #RemoteCache
FROM OPENQUERY(LinkedServer, 'SELECT id, col1, col2 FROM RemoteTable WHERE ...');

-- 再本地 JOIN
SELECT * FROM LocalTable L
JOIN #RemoteCache R ON L.id = R.id;

• 將「遠端掃描 + 網路傳輸 + 本地 JOIN」拆解為「遠端篩選 → 傳輸精簡結果 → 本地高效 JOIN」。
• 本地暫存表可建立索引、統計資料，JOIN 效能遠優於分散式查詢。
• 條件務必在遠端套用（WHERE 在 OPENQUERY 內）。

4. 處理跨版本 Collation 衝突

-- JOIN/比較欄位後加 COLLATE 強制統一
SELECT * FROM LocalTable L
JOIN #RemoteCache R ON L.code COLLATE Chinese_PRC_CI_AS = R.code COLLATE Chinese_PRC_CI_AS;

• SQL 6.5/2000/2005 預設 Collation 可能不同，導致比較失敗或隱性轉換掃描。
• 明確指定 COLLATE Chinese_PRC_CI_AS（或專案統一 Collation）消除歧義。
• 欄位定義階段即統一 Collation 為最佳實踐。

5. 極力減少 JOIN 數量與複雜度

• 每增加一個 JOIN，執行計畫搜尋空間指數級增大，優化器可能選擇次優計畫。
• 可透過：
  a. 預先彙總縮小維度表
  b. 應用層組裝替代多表 JOIN
  c. 物化視圖/快照表預計算常用 JOIN 結果
  來減少即時 JOIN。

6. 堅決拒絕游標，改用集合操作或批次迴圈

游標危害：逐列處理（RBAR），鎖定時間長、鎖升級風險高、計畫快取無法重用、平行度受限。

替代方案 A：集合操作（首選）

-- 以單一 UPDATE/INSERT/DELETE 替代游標迴圈
UPDATE TargetTable
SET status = 'Processed'
WHERE id IN (SELECT id FROM SourceTable WHERE condition);

替代方案 B：帶序號暫存表 + WHILE 迴圈（資料量 < 5000 列可接受）

-- 建立帶身分識別碼的暫存表
SELECT IDENTITY(INT,1,1) AS Seq, id, col1, col2
INTO #Batch
FROM SourceTable WHERE condition;

DECLARE @MaxSeq INT = (SELECT MAX(Seq) FROM #Batch);
DECLARE @CurSeq INT = 1;

WHILE @CurSeq <= @MaxSeq
BEGIN
  SELECT @id = id, @col1 = col1, @col2 = col2 FROM #Batch WHERE Seq = @CurSeq;
  -- 單筆處理邏輯
  SET @CurSeq = @CurSeq + 1;
END

• 每次迴圈只 SELECT 一列，避免游標持有鎖。
• 適合必須逐列呼叫外部 API、複雜程序邏輯無法集合化的場景。
• 資料量 > 5000 時效能明顯下降，尋找集合化重寫路徑。

總結

跨伺服器大量資料處理的核心哲學：把運算搬到資料旁邊（下推），把資料搬到運算旁邊（暫存表），用集合思維替代迴圈思維。這六大原則不僅適用於 SQL Server 2000，對現代雲端資料庫、分散式查詢引擎同樣有效。