Entity Framework生成的sp_executesql与SSMS中的直接查询之间的主要性能差异 [英] Major performance difference between Entity Framework generated sp_executesql and direct query in SSMS

问题描述

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

I'm using Entity Framework for making a rather large query. Recently this query is failing due to timeout exceptions.

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

When I started investigating this issue I used LinqPad and directly copied the SQL output in SSMS and ran the query. This query returns within 1 second!

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

The query then looks like (only for illustration, the real query is much larger)

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  

该查询完全相同，不同之处在于该查询被封装在对 sp_executesql 的调用内。像这样：

Now I used SQL Profiler to capture the real SQL send to the server. The query is exactly the same with the difference that this query is encapsulated within a call to sp_executesql. Like this:

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使用默认设置时会导致超时！

When I copy/paste this query in SSMS it runs for 60 seconds and thus results in a timeout when using from EF with default settings!

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

I can't wrap my head around why this difference is occurring, as this is the same query, the only thing is, it is executed differently.

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

I read a lot about why EF uses sp_executesql and I understand why. I also read that sp_executesql is different from EXEC because it makes use of the queryplan cache, but I don't understand why the SQL optimizer has such difficulty in creating a performant query plan for the sp_executesql version whereas it is capable of creating a performant queryplan for the direct query version.

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

I'm not sure if the complete query itself adds to the question. If it does, let me know and I will make an edit.

推荐答案

由于提供的注释，我管理了两件事：

Thanks to the supplied comments I managed two things:

• 我现在了解了查询计划以及查询中的参数嗅探和变量之间的区别


• 我实现了 DbCommandInterceptor 在需要时向查询添加 OPTION（优化未知）。

• I now understand the query plan and the differences between parameter sniffing and variables in queries
• I implemented a DbCommandInterceptor to add OPTION (OPTIMIZE FOR UNKNOWN) to the query when needed.

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

The SQL query compiled by Entity Framework can be intercepted before send to the server by adding an implementation to 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也缓存了查询，因此我检查是否已添加优化。

As Entity Framework also caches the queries, I check if an optimization already has been added.

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

But this approach will intercept all queries and obviously one should not do this. As the DbCommandInterceptionContext gives access to the DbContext I added an interface with a single property (ISupportQueryHints) to my DbContext which I set to a optimization when the query needs this.

现在看起来像这样：

 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);
    }
}

在需要的地方可以用作：

Where needed this can be used as:

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

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

Because my application makes use of a message based architecture surrounding commands and queries as described here my implementation consists of a decorator around the queryhandlers in need of optimization. This decorator sets the query hints to the DbContext whenever needed. This is however an implementation detail. The basic idea stays the same.

这篇关于Entity Framework生成的sp_executesql与SSMS中的直接查询之间的主要性能差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！