Ef 6 Parameter Sniffing

EF 6 Parameter Sniffing

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

EF Core Parameter Sniffing in SQL Server

And soon after I find the answer :)

As Gert says in the comment I had to pass a variable, so EF is doing some magic there.
So if I changed my code to this then it happens:

var myId = 6;

var result = ctx.Posts
    .Include(x => x.PostType)
    .Where(x => x.PostTypeId == myId && x.CreationDate >= new DateTime(2013, 01, 01))
        .Select(x => new {
            Id = x.Id,
            Body = x.Body, 
            Type = x.PostType.Type
        }).ToList();

I then get SQL like this

exec sp_executesql N'SELECT [p].[Id], [p].[Body], [p0].[Type]
FROM [Posts] AS [p]
INNER JOIN [PostTypes] AS [p0] ON [p].[PostTypeId] = [p0].[Id]
WHERE ([p].[PostTypeId] = @__myId_0) AND ([p].[CreationDate] >= ''2013-01-01T00:00:00.000'')',N'@__myId_0 int',@__myId_0=6

I can then execute another one with a different value

exec sp_executesql N'SELECT [p].[Id], [p].[Body], [p0].[Type]
FROM [Posts] AS [p]
INNER JOIN [PostTypes] AS [p0] ON [p].[PostTypeId] = [p0].[Id]
WHERE ([p].[PostTypeId] = @__myId_0) AND ([p].[CreationDate] >= ''2013-01-01T00:00:00.000'')',N'@__myId_0 int',@__myId_0=7

I can then see in the execution plan that the same query plan was used, as the top one has the first query that setup the plan then the second one uses it and keeps the estimated 166 rows but actually only got 4 rows back

Also I can confirm this if I look at the second queries properties, and can see it was compiled with 6 put was run with 7

Entity Framework 6 - Parameter query 11x slower than inline parameters query. Why?

The problem is that Entity Framework generates parameters of type DateTime2 while the actual database columns are defined as DateTime. There are two solutions:

Either change your database columns to DateTime2 or tell Entity Framework to use DateTime instead (see here).

How do I control parameter sniffing and/or query hints in entity framework?

To apply a hint on a query generate by EF, you should use plan guides, more info here: One to one join Not fast enough in SQL Server

How to add OPTION RECOMPILE into Entity Framework

I implemented IDbCommandInterceptor interface to fix the parameter sniffing error.

The usage of the new interceptor:

  using (var dataContext = new AdventureWorks2012Entities())
  {
    var optionRecompileInterceptor = new OptionRecompileInterceptor();
    DbInterception.Add(optionRecompileInterceptor);

    string city = "Seattle";
    var seattle = (
      from a in dataContext.Addresses
      where a.City == city
      select a).ToList();

    DbInterception.Remove(optionRecompileInterceptor); //Remove interceptor to not affect other queries
  }

Implementation of the interceptor:

public class OptionRecompileInterceptor : DbCommandInterceptor
{
  static void AddOptionToCommand(DbCommand command)
  {
    string optionRecompileString = "\r\nOPTION (RECOMPILE)";
    if (!command.CommandText.Contains(optionRecompileString)) //Check the option is not added already
    {
      command.CommandText += optionRecompileString;
    }
  }

  public override void NonQueryExecuting(
    DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
  {
    AddOptionToCommand(command);
  }

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

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

Entity Framework SELECT IN not using parameters

I can't say why EF (Core) designers decided to use constants instead of variables when translating Enumerable.Contains. As @Gert Arnold pointed out in the comments, could be related to SQL query parameter count limit.

Interestingly, both EF (6.2) and EF Core (2.1.2) generate IN with parameters when you use the equivalent || expression like:

var values = new int[] { 1, 2, 3 };
var value0 = values[0];
var value1 = values[1];
var value2 = values[2]; 
var query = context.Things.Where(x =>
    x.Id == value0 ||
    x.Id == value1 ||
    x.Id == value2);

EF6.2 generated query is

SELECT
    [Extent1].[Id] AS [Id]
    FROM [dbo].[Things] AS [Extent1]
    WHERE [Extent1].[Id] IN (@p__linq__0,@p__linq__1,@p__linq__2)

EF Core 2.1 does something similar.

So the solution is to convert the Contains expression to || based expression. It has to be dynamically using Expression class methods. And to make it easier to use, could be encapsulated in a custom extension method, which internally user ExpressionVisitor to perform the conversion.

Something like this:

public static partial class EfQueryableExtensions
{
    public static IQueryable<T> Parameterize<T>(this IQueryable<T> source)
    {
        var expression = new ContainsConverter().Visit(source.Expression);
        if (expression == source) return source;
        return source.Provider.CreateQuery<T>(expression);
    }

    class ContainsConverter : ExpressionVisitor
    {
        protected override Expression VisitMethodCall(MethodCallExpression node)
        {
            if (node.Method.DeclaringType == typeof(Enumerable) &&
                node.Method.Name == nameof(Enumerable.Contains) &&
                node.Arguments.Count == 2 &&
                CanEvaluate(node.Arguments[0]))
            {
                var values = Expression.Lambda<Func<IEnumerable>>(node.Arguments[0]).Compile().Invoke();
                var left = Visit(node.Arguments[1]);
                Expression result = null;
                foreach (var value in values)
                {
                    // var variable = new Tuple<TValue>(value);
                    var variable = Activator.CreateInstance(typeof(Tuple<>).MakeGenericType(left.Type), value);
                    // var right = variable.Item1;
                    var right = Expression.Property(Expression.Constant(variable), nameof(Tuple<int>.Item1));
                    var match = Expression.Equal(left, right);
                    result = result != null ? Expression.OrElse(result, match) : match;
                }
                return result ?? Expression.Constant(false);
            }
            return base.VisitMethodCall(node);
        }

        static bool CanEvaluate(Expression e)
        {
            if (e == null) return true;
            if (e.NodeType == ExpressionType.Convert)
                return CanEvaluate(((UnaryExpression)e).Operand);
            if (e.NodeType == ExpressionType.MemberAccess)
                return CanEvaluate(((MemberExpression)e).Expression);
            return e.NodeType == ExpressionType.Constant;
        }
    }
}

Applying it to the sample query

var values = new int[] { 1, 2, 3 };
var query = context.Things
    .Where(x => values.Contains(x.Id))
    .Parameterize();

produces the desired translation.

---

Related Topics