萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server 查詢優化實戰技巧:從 Access 遷移到 SQL Server 的效能陷阱與解法

從 Access/Jet 遷移至 SQL Server 時,原本的查詢寫法常成為效能殺手。本文針對三類常見問題提供改寫模式與原理解析。

技巧一:LIKE 模糊查詢導致 Access 記憶體溢位

問題現象

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萬列)、Access 連線、特殊字元導致引擎 Bug
  • 不適用:大資料量、SQL Server (引擎無此問題)

技巧二:多關鍵字搜尋 (類 Google/百度 空格分隔)

需求

輸入「關鍵字1 關鍵字2」→ 同時包含兩詞 (AND 邏輯) 或 任一詞 (OR 邏輯)。

動態 SQL 組裝模式

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

參數化防注入版 (SQL Server)

-- 建立分割函數回傳表值
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 vs LIKE 效能迷思

  • 實測證明:CHARINDEX('abc', Col) > 0 **不優於** Col LIKE '%abc%',同樣導致掃描且**失去索引**
  • 前綴搜尋 LIKE 'abc%' 才能 Seek 索引

綜合優化檢查清單

  1. 執行計畫:是否有 Scan、Key Lookup、Sort、Hash Match 警告
  2. 統計資料:DBCC SHOW_STATISTICS、UPDATE STATISTICS ... WITH FULLSCAN
  3. 索引設計:缺失索引 DMV (sys.dm_db_missing_index_*)、重複/未用索引
  4. 參數化:強制參數化 / sp_executesql / 計畫指南
  5. 分頁:OFFSET/FETCH 或 Keyset Pagination
  6. 分區表/檢視:大表水平切分

優化是「測量 → 分析 → 改寫 → 驗證」的科學循環,而非經驗主義猜測。

copyright © 萬盛學電腦網 all rights reserved