EF Core基于左连接属性排序的查询执行缓慢（生成SQL在SSMS中执行快速）求助

首先得给你点个赞，能通过XEvent Profiler排查出实际执行的SQL，还定位到参数嗅探的问题，这已经解决了大半疑惑！我来帮你梳理下整个问题的来龙去脉，再补充些优化思路：

1. 为什么EF执行和SSMS直接跑SQL性能差这么多？ #

一开始你被调试控制台的SQL和LINQPad的执行结果误导了，以为是ROW_NUMBER的锅，但实际用XEvent Profiler确认后，EF确实生成了带OFFSET/FETCH的正确SQL。那性能差异的核心原因就是参数嗅探：

• SSMS直接执行时，SQL Server会根据当前的字面量（比如OFFSET 0）生成最优执行计划；
• 但EF用的是参数化查询（@p0=0, @p1=100），SQL Server会缓存这个参数对应的执行计划。如果缓存的计划不适合后续的参数（比如当Offset很大时），或者这个边界参数（0）的计划本身就不是最优的，就会导致执行时间暴增。

2. 解决参数嗅探的可行方案 #

你用拦截器添加OPTION (RECOMPILE)的思路完全正确，这会强制SQL Server每次执行查询时都重新生成最优计划，绕过缓存的不合适计划。这里给你补个完整的拦截器实现示例：

public class RecompileQueryInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result)
    {
        // 只给SELECT语句添加RECOMPILE，避免影响其他操作
        if (command.CommandText.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase) 
            && !command.CommandText.Contains("OPTION (RECOMPILE)", StringComparison.OrdinalIgnoreCase))
        {
            command.CommandText += " OPTION (RECOMPILE)";
        }
        return base.ReaderExecuting(command, eventData, result);
    }
}

然后在你的DbContext配置中注册这个拦截器：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer("你的连接字符串")
        .AddInterceptors(new RecompileQueryInterceptor());
}

3. 进一步优化的思路（如果RECOMPILE还不够） #

300万+数据的分页查询，即使有索引，排序+关联的开销还是不小。你考虑用SOLR这类搜索引擎是非常合理的方向，因为搜索引擎天生擅长处理大规模数据的排序、分页和检索。除此之外，还可以试试这些数据库层面的优化：

• 优化索引策略：
  确保你有合适的覆盖索引，避免SQL Server回表查询。比如：
  • 在Category表上创建Name的非聚集索引；
  • 在Asset表上创建CategoryId, Name的复合非聚集索引，这样SQL Server可以直接通过索引完成排序和筛选，不用扫描整个表。
• 替换Offset/Fetch为键集分页：
  Offset/Fetch在Offset值很大时性能会急剧下降，因为SQL Server需要先扫描前面所有的行。键集分页（Key Set Pagination）是更好的方案：记录上一页最后一条数据的Category.Name和Asset.Name，下次查询用条件过滤：

  // 假设上一页最后一条的分类名是lastCategoryName，资产名是lastAssetName
  dBcontext.Assets
      .Where(x => x.Category.Name > lastCategoryName 
                  || (x.Category.Name == lastCategoryName && x.Name > lastAssetName))
      .OrderBy(x => x.Category.Name)
      .ThenBy(x => x.Name)
      .Take(100)
      .AsNoTracking()
      .Select(x => x.Name)
      .ToList();
  

  这种方式可以利用索引直接定位到起始位置，避免扫描大量无关行。
• 冗余字段减少关联：
  如果Category.Name不经常变更，可以把这个字段冗余到Asset表中（比如加个CategoryName字段），这样查询时不需要左连接Category表，直接用Asset.CategoryName排序，能大幅减少关联开销，同时创建CategoryName, Name的复合索引，性能会更优。

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