背景：SP 多、無規範 → 維護困難、效能差異達百倍
經驗：**優化後 SP 效能可達未優化版數百倍**。規範化編寫與系統性優化並重。
一、跨庫存取規範
- **嚴禁直接引用 `Database.dbo.Table`**。
- **當前庫建立 View 封裝跨庫邏輯**，SP 只查 View。
理由：sp_depends 無法追蹤跨庫相依，影響影響分析與重構。
二、提交前自我檢核
- **必須執行 `SET SHOWPLAN ON` 分析查詢計畫**，完成自查優化再提交。
三、高效能 SP 編寫鐵律 (3 大面向)
(A) SQL 使用規範
- 避免大事務、慎用 HOLDLOCK → 提升併發。
- 避免重複存取同大表 → 先依條件篩選進 **暫存表**，再連接。
- 避免游標；>1 萬列必改寫；用了游標**嚴禁迴圈內再 JOIN**。
- WHERE 條件順序：依索引順序、選擇性高到低** (優化器雖自動優化，但明確順序利於穩定計畫)。
- WHERE 左邊勿加函數/運算 (
ABS(col)>5 ❌) → 索引失效。
- 存在性判斷用 `EXISTS` 而非 `COUNT(1)`；統計全表才用 `COUNT`，且 `COUNT(1)` 優於 `COUNT(*)` (舊版觀念，新版相同)。
- 優用 `>=` 而非 `>` (邊界包含利於索引範圍掃描)。
- OR/UNION 互換需測試 (參考索引實戰篇經驗法則)。
- JOIN 欄位資料型別必須一致** → 隱式轉換導致索引失效。
- 參數與欄位型別一致** → 避免隱式轉換。
- INSERT/UPDATE 大量資料 (>200 頁/400KB) 觸發鎖升級 (頁鎖→表鎖)** → 分批處理。
(B) 索引使用規範
- 大型 OLTP 表**索引數 ≤ 6**。
- **盡可能用索引欄位作查詢條件**，特別是**聚集索引**；必要時 `INDEX(index_name)` 強制指定。
- 避免大表 Table Scan，**必要時新增索引**。
- **複合索引必須用到第一欄位** 才會被使用。
- **週期性重建索引、重新編譯 SP** (`sp_recompile` / `UPDATE STATISTICS` / `DBCC FREEPROCCACHE`)。
(C) tempdb 使用規範
- **盡量避免 `DISTINCT`、`ORDER BY`、`GROUP BY`、`HAVING`、`JOIN`、`COMPUTE`** → 增加 tempdb 負擔。
- 避免頻繁建/刪暫存表 → 系統表資源耗盡。
- 大量資料一次性插入暫存表 → **`SELECT INTO` 替代 `CREATE TABLE`** (最小化記錄、極快)；小量 → `CREATE TABLE` + `INSERT` (緩和系統表壓力)。
- 大暫存表需索引 → **建表+建索引放入獨立子 SP** → 確保編譯時統計資訊正確，主 SP 能用到索引。
- SP 結束**顯式清理暫存表**：`TRUNCATE TABLE` → `DROP TABLE` (避免系統表長時間鎖定)。
- 慎用大暫存表 JOIN 大表 → 單陳述式多次存取 tempdb 系統表。
四、合理演算法選擇與調優工具
- 結合上述技術 + ASE Tuning 手冊，**多種演算法實測比較**，取資源最少、效率最高者。
- 核心調優指令：
SET STATISTICS IO ON、SET STATISTICS TIME ON、SET SHOWPLAN ON (或 SSMS 圖形化執行計畫)。
**規範是效能的基石，測試是優化的證據。** 將這份清單內化為團隊編碼標準，配合 Code Review 與自動化靜態分析，讓「寫出高效 SP」成為預設行為。
