EF Core执行ToList()极慢，手动运行生成的SQL却秒出结果

场景复现 #

编写的EF Core查询代码如下：

var query = context.Set<TEntity>()
    .AsNoTracking()
    .Where(x => ids.Contains(x.Id1) && x.Id2 != x.Id1 && (x.Status != "Status" || x.Status == null))
    .OrderBy(x => x.Field1)
    .Take(10);

其中ids类型为IEnumerable<Guid?>。

• 在排序和Take操作前执行Count()，结果仅需400ms返回；
• 执行排序和Take后的ToList()，耗时超过30秒。

生成SQL对比 #

通过ToQueryString()生成的SQL如下，手动在Azure Data Studio中运行仅需不到1秒：

DECLARE @__p_1 int = 10;
DECLARE @__ids_0 nvarchar(4000) = N'["890217ea-ad02-4b9f-9f9d-734a72e9fb04"]';

SELECT TOP(@__p_1) [w].*
FROM [TEntity] AS [w]
WHERE EXISTS (
    SELECT 1
    FROM OPENJSON(@__ids_0) WITH ([value] uniqueidentifier '$') AS [m]
    WHERE [m].[value] = [w].[Id1] OR ([m].[value] IS NULL AND [w].[Id1] IS NULL)) AND ([w].[Id2] <> [w].[Id1] OR [w].[Id1] IS NULL) AND ([w].[Status] <> 'Status' OR [w].[Status] IS NULL OR [w].[Status] IS NULL)
ORDER BY [w].[Field1]

但拦截到EF Core实际执行的是带sp_executesql的版本，这个版本执行耗时超30秒：

exec sp_executesql N'SELECT TOP(@__p_1) [w].*
FROM [TEntity] AS [w]
WHERE EXISTS (
    SELECT 1
    FROM OPENJSON(@__ids_0) WITH ([value] uniqueidentifier ''$'') AS [m]
    WHERE [m].[value] = [w].[Id1] OR ([m].[value] IS NULL AND [w].[Id1] IS NULL)) AND ([w].[Id2] <> [w].[Id1] OR [w].[Id1] IS NULL) AND ([w].[Status] <> ''Status'' OR [w].[Status] IS NULL OR [w].[Status] IS NULL)
ORDER BY [w].[Field1]',N'@__p_1 int,@__ids_0 nvarchar(4000)',@__p_1=10,@__ids_0=N'["890217ea-ad02-4b9f-9f9d-734a72e9fb04"]'

原因解析 #

核心问题是SQL Server参数嗅探：

• 当使用sp_executesql带参数执行时，SQL Server会缓存第一次执行生成的执行计划，后续同语句不同参数的查询会复用该计划。如果缓存的执行计划是针对某类参数（比如大量ID的场景）生成的，当后续使用少量ID参数查询时，复用的计划就会非常低效，导致查询变慢。
• 而直接执行DECLARE变量的SQL时，SQL Server会基于当前变量的实际值生成针对性的最优执行计划，不存在计划复用不匹配的问题。

解决方法 #

实现自定义DbCommandInterceptor，通过扩展方法标记需要优化的查询，在生成的SQL末尾注入OPTION (OPTIMIZE FOR UNKNOWN)提示。该提示会让SQL Server不依赖具体参数值生成通用执行计划，避免参数嗅探导致的性能问题。

内容的提问来源于stack exchange，提问作者UNeverNo