從 Access/Jet 遷移至 SQL Server 時,原本的查詢寫法常成為效能殺手。本文針對三類常見問題提供改寫模式與原理解析。
Access 查詢 WHERE Field LIKE '%keyword%' 遇日文片假名/特殊字元 → 記憶體溢位錯誤。
' 原始 (Access 崩潰)
sql = "SELECT * FROM Ada WHERE Alice LIKE '%" & Keyword & "%'"
' 改寫:資料庫只撈回候選集,應用層 Filter
sql = "SELECT * FROM Ada"
rs.Open sql, conn
rs.Filter = "Alice LIKE '%" & Keyword & "%'" -- ADO Recordset Filter
' 後續處理 rs...
輸入「關鍵字1 關鍵字2」→ 同時包含兩詞 (AND 邏輯) 或 任一詞 (OR 邏輯)。
keywords = Split(Trim(Keyword), " ")
sql = "SELECT * FROM Ada WHERE "
For i = 0 To UBound(keywords)
If i > 0 Then sql = sql & " AND " -- 或 OR
sql = sql & "(Alice LIKE '%" & keywords(i) & "%' OR Alice1 LIKE '%" & keywords(i) & "%')"
Next
-- 建立分割函數回傳表值
CREATE FUNCTION dbo.SplitString (@str NVARCHAR(MAX), @sep NCHAR(1))
RETURNS @t TABLE (val NVARCHAR(4000)) AS BEGIN ... END;
-- 查詢
DECLARE @Keywords NVARCHAR(MAX) = N'關鍵字1 關鍵字2';
SELECT DISTINCT A.*
FROM Ada A
CROSS APPLY dbo.SplitString(@Keywords, N' ') K
WHERE A.Alice LIKE '%' + K.val + '%' OR A.Alice1 LIKE '%' + K.val + '%';
| 反模式 | 原因 | 修正 |
|---|---|---|
WHERE Col OR Col2 = ... | OR 導致掃描 | UNION ALL 分開查詢合併 |
WHERE Col LIKE '%abc%' | 前置萬用字元 | 全文檢索 / LIKE 'abc%' / 反轉索引 |
WHERE Col = @param | 參數嗅探/編譯時未知 | OPTION (RECOMPILE) / OPTIMIZE FOR UNKNOWN / 強制索引 |
WHERE Col/2 = 100 | 欄位運算 | WHERE Col = 200 |
WHERE SUBSTRING(Col,1,3)='abc' | 欄位函數 | WHERE Col LIKE 'abc%' |
WHERE DATEDIFF(day, Dt, '2024')=0 | 欄位函數 | WHERE Dt >= '2024-01-01' AND Dt < '2024-01-02' |
WHERE ISNULL(Col, '') = '' | 函數包裹 | WHERE Col IS NULL OR Col = '' |
CHARINDEX('abc', Col) > 0 **不優於** Col LIKE '%abc%',同樣導致掃描且**失去索引**LIKE 'abc%' 才能 Seek 索引DBCC SHOW_STATISTICS、UPDATE STATISTICS ... WITH FULLSCANsys.dm_db_missing_index_*)、重複/未用索引sp_executesql / 計畫指南優化是「測量 → 分析 → 改寫 → 驗證」的科學循環,而非經驗主義猜測。