Entity Framework生成的sp_executesql与SSMS中的直接查询之间的主要性能差异

Question

我正在使用Entity Framework进行较大的查询。最近，由于超时异常，此查询失败。

当我开始调查此问题时，我使用LinqPad并直接在SSMS中复制SQL输出并运行查询。该查询将在1秒内返回！

然后查询看起来像（仅用于说明，实际查询要大得多）

DECLARE @p__linq__0 DateTime2 = '2017-10-01 00:00:00.0000000'
DECLARE @p__linq__1 DateTime2 = '2017-10-31 00:00:00.0000000'

SELECT 
    [Project8].[Volgnummer] AS [Volgnummer], 
    [Project8].[FkKlant] AS [FkKlant], 
    -- rest omitted for brevity

现在，我使用SQL事件探查器捕获了发送到服务器的真实SQL。该查询完全相同，不同之处在于该查询被封装在对的调用中sp_executesql。像这样：

exec sp_executesql N'SELECT 
    [Project8].[Volgnummer] AS [Volgnummer], 
    [Project8].[FkKlant] AS [FkKlant], 
    -- rest omitted for brevity  
    ',N'@p__linq__0 datetime2(7),@p__linq__1 datetime2(7)',
        @p__linq__0='2017-10-01 00:00:00',@p__linq__1='2017-10-31 00:00:00'

当我在SSMS中复制/粘贴此查询时，它会运行60秒，因此在默认设置下从EF使用时会导致超时！

我无法确定为什么会出现这种差异，因为这是相同的查询，唯一的是，它的执行方式不同。

我读了很多关于EF为什么使用sp_executesql的知识，并且我理解为什么。我还读到sp_executesql与EXEC有所不同，因为它利用了queryplan缓存，但是我不明白为什么SQL优化器在为sp_executesql版本创建高效的查询计划时却如此困难，而它却能够创建高效的查询计划。直接查询版本。

我不确定完整的查询本身是否会增加问题。如果是这样，请告诉我，然后进行编辑。

Answer 1

多亏了提供的注释，我可以完成两件事：

• 我现在了解了查询计划以及查询中的参数嗅探和变量之间的区别
• 我实现了在需要时DbCommandInterceptor添加OPTION (OPTIMIZE FOR UNKNOWN)到查询中的功能。

通过将实现添加到，可以拦截由Entity Framework编译的SQL查询，然后再将其发送到服务器DbInterception。

这样的实现很简单：

public class QueryHintInterceptor : DbCommandInterceptor
{
    public override void ReaderExecuting(DbCommand command, 
        DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        queryHint = " OPTION (OPTIMIZE FOR UNKNOWN)";
        if (!command.CommandText.EndsWith(queryHint))
        {
            command.CommandText += queryHint;
        }

        base.ReaderExecuting(command, interceptionContext);
    }
}
// Add to the interception proces:
DbInterception.Add(new QueryHintsInterceptor());

由于Entity Framework也缓存查询，因此我检查是否已添加优化。

但是这种方法将拦截所有查询，而且显然不应该这样做。随着对的DbCommandInterceptionContext访问，DbContext我为我添加了一个具有单个属性（ISupportQueryHints）的接口，DbContext当查询需要此接口时，我将该接口设置为优化。

现在看起来像这样：

 public class QueryHintInterceptor : DbCommandInterceptor
{
    public override void ReaderExecuting(DbCommand command, 
        DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        var dbContext =
            interceptionContext.DbContexts.FirstOrDefault(d => d is ISupportQueryHints) as ISupportQueryHints;

        if (dbContext != null)
        {
            var queryHint = $" OPTION ({dbContext.QueryHint})";
            if (!command.CommandText.EndsWith(queryHint))
            {
                command.CommandText += queryHint;
            }
        }

        base.ReaderExecuting(command, interceptionContext);
    }
}

在需要的地方可以用作：

public IEnumerable<SomeDto> QuerySomeDto()
{
    using (var dbContext = new MyQuerySupportingDbContext())
    {
        dbContext.QueryHint = "OPTIMIZE FOR UNKNOWN";
        return this.PerformQuery(dbContext);
    }
}

因为我的应用程序利用了围绕命令和查询的基于消息的体系结构（如此处所述），所以我的实现由需要优化的查询处理程序周围的装饰器组成。必要时，此装饰器将查询提示设置为DbContext。但是，这是一个实现细节。基本思想保持不变。