EF 6 Parameter Sniffing

Question

I have a dynamic query that is just too large to put here. Safe to say that in it's current form it utilizes a CLR procedure to dynamically build joins based upon the number of search parameters passed then takes that result and joins it to more detailed tables to bring back attributes important to the end-user. I have converted the entire query into LINQ to Entities and what I have found is that the SQL that it produces is efficient enough to do the job, however running via EF 6, the query timesout. Taking the resulting SQL and running it in SSMS runs in 3 or less seconds. I can only imagine that my problem is parameter sniffing. I have tried updating statistics on every table in the database and this has not solved the problem.

My Question is:

Can I somehow embed options like an "OPTION RECOMPILE" via EF?

Answer 1

It's possible to use the interception feature of EF6 to manipulate its internal SQL commands before executing them on DB, for instance adding option(recompile) at the end of the command:

public class OptionRecompileHintDbCommandInterceptor : IDbCommandInterceptor
{
    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<Int32> interceptionContext)
    {
    }

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        addQueryHint(command);
    }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        addQueryHint(command);
    }

    private static void addQueryHint(IDbCommand command)
    {
        if (command.CommandType != CommandType.Text || !(command is SqlCommand))
            return;

        if (command.CommandText.StartsWith("select", StringComparison.OrdinalIgnoreCase) && !command.CommandText.Contains("option(recompile)"))
        {
            command.CommandText = command.CommandText + " option(recompile)";
        }
    }
}

To use it, add the following line at the beginning of the application:

DbInterception.Add(new OptionRecompileHintDbCommandInterceptor());

Answer 2

I like VahidN's solution, do up vote him, but I want more control of when it happens. It turns out that DB Interceptors are very global, and I only wanted this to happen on specific contexts in specific scenarios.

Here we are setting the ground work to also support adding other query hints, that could be turned on and off as desired.

Since I often expose the method for passing a connection string, I also included support for that.

Below would give your context a flag to enable/disable the hint programatically, by extending the partial class EF generates. We also threw the small piece of reused code in the Interceptor into its own method.

Small Interface

public interface IQueryHintable
{
    bool HintWithRecompile { get; set; }
}

DB Command Interceptor

public class OptionHintDbCommandInterceptor : IDbCommandInterceptor
{
    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<Int32> interceptionContext)
    {
        AddHints(command, interceptionContext);
    }

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        AddHints(command, interceptionContext);
    }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        AddHints(command, interceptionContext);
    }

    private static void AddHints<T>(DbCommand command, DbCommandInterceptionContext<T> interceptionContext)
    {
        var context = interceptionContext.DbContexts.FirstOrDefault();
        if (context is IQueryHintable)
        {
            var hints = (IQueryHintable)context;

            if (hints.HintWithRecompile)
            {
                addRecompileQueryHint(command);
            }
        }
    }

    private static void addRecompileQueryHint(IDbCommand command)
    {
        if (command.CommandType != CommandType.Text || !(command is SqlCommand))
            return;

        if (command.CommandText.StartsWith("select", StringComparison.OrdinalIgnoreCase) && !command.CommandText.Contains("option(recompile)"))
        {
            command.CommandText = command.CommandText + " option(recompile)";
        }
    }
}

Extending Entity Context to Add IQueryHintable

public partial class SomeEntities : DbContext, IQueryHintable
{
    public bool HintWithRecompile { get; set; }

    public SomeEntities (string connectionString, bool hintWithRecompile) : base(connectionString)
    {
        HintWithRecompile = hintWithRecompile;
    }

    public SomeEntities (bool hintWithRecompile) : base()
    {
        HintWithRecompile = hintWithRecompile;
    }

    public SomeEntities (string connectionString) : base(connectionString)
    {
    }

}

Register DB Command Interceptor (global.asax)

    DbInterception.Add(new OptionHintDbCommandInterceptor());

Enable context wide

    using(var db = new SomeEntities(hintWithRecompile: true) )
    {
    }

Turn On or Off

    db.HintWithRecompile = true;
    // Do Something
    db.HintWithRecompile = false;

I called this HintWithRecompile, because you might also want to implement a HintOptimizeForUnknown , or other query hints.