424 字
2 分鐘
EF Core 查詢忽快忽慢？用 RECOMPILE 解掉 Parameter Sniffing
結論
- 用
DbCommandInterceptor + TagWith，即可在 EF Core 查詢加上OPTION(RECOMPILE)，快速避開 Parameter Sniffing。
適合用在哪裡
- 查詢「同一支 SQL，但速度不穩」的場景
- EF Core 查詢 timeout，但 SSMS 正常
- 條件差異大（例如：熱門 vs 冷門資料）
- 不想改 SQL，但需要快速解法
流程步驟
1. 註冊 Interceptor
- 在 DbContext 注入自訂 Interceptor
- 讓 EF Core 有機會攔截 SQL 並改寫
builder.UseSqlServer(connectionString) .UseRecompileExtensions(); // 註冊攔截器2. 在查詢標記 RECOMPILE
- 用
TagWith做標記（EF Core 內建） - 讓 Interceptor 知道哪段 SQL 要處理
var name = "SomeName";var result = context.SomeItems .Where(x => x.Name == name) .WithRecompile() // 加上標記 .ToList();實際 SQL：
SELECT [s].[Id], [s].[Name]FROM [SomeItems] AS [s]WHERE [s].[Name] = @__name_0OPTION(RECOMPILE)3. 實作 Interceptor 改寫 SQL
- 偵測 Tag
- 移除 comment 並補上
OPTION(RECOMPILE)
public static class RecompileExtensions{ private const string RecompileTag = "recompile_query_tag"; private const string RecompileComment = "-- " + RecompileTag + "\\r\\n";
public static DbContextOptionsBuilder UseRecompileExtensions(this DbContextOptionsBuilder builder) { return builder.AddInterceptors(RecompileInterceptor.Instance); }
public static IQueryable<T> WithRecompile<T>(this IQueryable<T> query) { return query.TagWith(RecompileTag); }
private class RecompileInterceptor : DbCommandInterceptor { public static RecompileInterceptor Instance = new();
public override InterceptionResult<DbDataReader> ReaderExecuting( DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result) { CorrectCommand(command); return base.ReaderExecuting(command, eventData, result); }
private static void CorrectCommand(DbCommand command) { var newQuery = command.CommandText.Replace(RecompileComment, "");
if (!ReferenceEquals(newQuery, command.CommandText)) { if (newQuery.StartsWith("\\r\\n")) newQuery = newQuery.Substring(2);
newQuery += "\\r\\nOPTION(RECOMPILE)"; command.CommandText = newQuery; } } }}補充
RECOMPILE每次都重新產生執行計畫，CPU 成本會增加，不適合高頻查詢- 適合「查詢不穩」而不是「查詢本身慢」
- 若是全域問題，可考慮 DB 層級關閉 Parameter Sniffing
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;指令 / 範例整理
Click to expand
// 註冊builder.UseRecompileExtensions();
// 使用query.WithRecompile().ToList();-- DB 層級關閉 Parameter SniffingALTER DATABASE SCOPED CONFIGURATIONSET PARAMETER_SNIFFING = OFF;收尾
- 當 EF 跟 SQL 表現不一致時，這招通常能快速止血；但長期還是要回頭優化索引與查詢。
參考來源
EF Core 查詢忽快忽慢？用 RECOMPILE 解掉 Parameter Sniffing
https://joyceowo.github.io/posts/23ba78ea09fa8171ba34eeb03351b2a5/