Major performance difference between Entity Framework generated sp_executesql and direct query in SSMS

user_3294832 0
Viewed 848

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

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

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'

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.

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.

entity-framework sql-server sql
2 Answers

Thanks to the supplied comments I managed two things:

  • 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.

The SQL query compiled by Entity Framework can be intercepted before send to the server by adding an implementation to DbInterception.

Such an implementation is trivial to make:

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

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

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.

This now looks like 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);
    }
}

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.

user_32948320

I updated @Ric.Net's QueryHintInterceptor class to handle the case where multiple contexts are being used for a query and may have their own hints:

public class QueryHintInterceptor : DbCommandInterceptor
{
    public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        var contextHints = interceptionContext.DbContexts
            .Select(c => (c as ISupportQueryHints)?.QueryHint)
            .Where(h => !string.IsNullOrEmpty(h))
            .Distinct()
            .ToList();

        var queryHint = $"{System.Environment.NewLine}OPTION ({ string.Join(", ", contextHints) })";

        if (contextHints.Any() && !command.CommandText.EndsWith(queryHint))
        {
            command.CommandText += queryHint;
        }

        base.ReaderExecuting(command, interceptionContext);
    }
}

Although honestly, if you're at that point, you might consider building a more robust solution like the one described here.

user_61667810
Related