情境
Table1 (500 萬列) 需依 Col1, Col2 關聯 Table2 (4 萬列) 取得 Col3。Table2 為靜態查閱表，更新極少。傳統 JOIN 即使有索引，仍需重複 B-Tree 搜尋，CPU/IO 成本高。
優化演進
| 方案 | 做法 | 耗時 (500萬 × 4萬) |
|---|
| 1. 獨立索引 | Col1、Col2 各建非叢集索引 | 106 秒 |
| 2. 複合索引 | (Col1, Col2) 複合索引 | — |
| 3. 包含索引 | (Col1, Col2) INCLUDE (Col3) | 45 秒 |
| 4. CLR UDT 記憶體快取 | 啟動時將 Table2 全量載入 static Dictionary | 33 秒 |
CLR 實作核心碼 (C#)
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
public partial class UserDefinedFunctions
{
// 靜態字典：Key = "Col1+Col2"，Value = Col3
private static readonly Dictionary Cache = new Dictionary();
// 靜態建構式：SQL Server 服務啟動時自動執行一次
static UserDefinedFunctions()
{
LoadCache();
}
private static void LoadCache()
{
using (var conn = new SqlConnection("context connection=true"))
using (var cmd = new SqlCommand("SELECT Col1, Col2, Col3 FROM dbo.LookupTable ORDER BY Col1, Col2", conn))
{
conn.Open();
using (var rd = cmd.ExecuteReader())
{
while (rd.Read())
{
string key = rd.GetString(0) + "+" + rd.GetString(1);
if (!Cache.ContainsKey(key))
Cache.Add(key, rd.GetString(2));
}
}
}
}
[SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlString LookupValue(string col1, string col2)
{
string key = col1 + "+" + col2;
string val;
return Cache.TryGetValue(key, out val) ? new SqlString(val) : SqlString.Null;
}
}
部署步驟
- VS 建立 SQL Server Database Project，加入上述類別
- 專案屬性 → Database → 目標框架
.NET 3.5、權限 SAFE
- 建置 → 在 SSMS 執行產生的
CREATE ASSEMBLY / CREATE FUNCTION 腳本
- 啟用 CLR：
EXEC sp_configure 'clr enabled', 1; RECONFIGURE
呼叫方式對比
-- 傳統 JOIN (包含索引)
SELECT @rc = Col3 FROM LookupTable WHERE Col1 = @val1 AND Col2 = @val2
-- CLR 函數
SELECT @rc = dbo.LookupValue(@val1, @val2)
優缺點總結
| 優點 | 缺點/限制 |
|---|
| 記憶體 O(1) 查找，效能最佳 | Table2 更新需重新部署組件或重啟服務 |
| 封裝複雜邏輯 (字串處理、加密等) | Key 衝突風險：Col1+Col2 必須唯一，否則需改用 List/實體 |
| 程式碼加密，提高安全性 | 部署/維護成本較高 (需 VS、DLL 管理) |
解決更新問題
- 在 LookupTable 建立
AFTER INSERT/UPDATE/DELETE TRIGGER，呼叫 sp_refreshsqlmodule 重新載入組件 (或重啟服務)
- 或改用
Memory-Optimized Table (SQL 2014+) 原生支援記憶體優化
