c# - tutorial - EF 6 Parameter Sniffing

entity framework homepage

Question

Tengo una consulta dinámica que es demasiado grande para poner aquí. Es seguro decir que, en su forma actual, utiliza un procedimiento CLR para construir juntas dinámicamente en función de la cantidad de parámetros de búsqueda pasados, luego toma ese resultado y lo une a tablas más detalladas para devolver atributos importantes para el usuario final. He convertido toda la consulta en LINQ a Entidades y lo que he encontrado es que el SQL que produce es lo suficientemente eficiente para hacer el trabajo, sin embargo, se ejecuta a través de EF 6, el tiempo de espera de la consulta. Tomar el SQL resultante y ejecutarlo en SSMS se ejecuta en 3 o menos segundos. Solo puedo imaginar que mi problema es olfatear los parámetros. He intentado actualizar las estadísticas en todas las tablas de la base de datos y esto no ha resuelto el problema.

Mi pregunta es:

¿De alguna forma puedo incorporar opciones como "OPTION RECOMPILE" a través de EF?

Answer 1

Es posible utilizar la función de interceptación de EF6 para manipular sus comandos SQL internos antes de ejecutarlos en DB, por ejemplo, agregar la option(recompile) al final del comando:

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

Para usarlo, agregue la siguiente línea al comienzo de la aplicación:

DbInterception.Add(new OptionRecompileHintDbCommandInterceptor());

Answer 2

Tuve un problema similar. Al final, eliminé el plan de consulta en caché con este comando:

dbcc freeproccache([your plan handle here])

Para obtener el control de su plan, puede usar la siguiente consulta:

SELECT qs.plan_handle, a.attrlist, est.dbid, text
FROM   sys.dm_exec_query_stats qs
CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
CROSS  APPLY (SELECT epa.attribute + ''='' + convert(nvarchar(127), epa.value) + ''   ''
      FROM   sys.dm_exec_plan_attributes(qs.plan_handle) epa
      WHERE  epa.is_cache_key = 1
      ORDER  BY epa.attribute
      FOR    XML PATH('''')) AS a(attrlist)
 WHERE  est.text LIKE ''%standardHourRate%'' and est.text like ''%q__7%''and est.text like ''%Unit Overhead%''
 AND  est.text NOT LIKE ''%sys.dm_exec_plan_attributes%''

reemplazando el contenido de las cláusulas ''me gusta'' con las partes apropiadas de su consulta.

Puedes ver mi problema completo en:

La consulta SQL mediante Entity Framework se ejecuta más lentamente, utiliza un plan de consulta incorrecto

Answer 3

Me gusta la solución de VahidN , no lo vote, pero quiero más control sobre cuándo sucede. Resulta que DB Interceptors es muy global, y solo quería que esto sucediera en contextos específicos en escenarios específicos.

Aquí estamos estableciendo el trabajo preliminar para que también sea compatible con agregar otras sugerencias de consulta, que podrían activarse y desactivarse según se desee.

Como a menudo expongo el método para pasar una cadena de conexión, también incluí soporte para eso.

A continuación, le daría a su contexto una bandera para habilitar / deshabilitar la sugerencia de forma programática, extendiendo la clase parcial que genera EF. También lanzamos la pequeña pieza de código reutilizado en el Interceptor en su propio método.

Interfaz pequeña

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

Interceptor de comando DB

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

Entorno de entidad extendido para agregar 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)
    {
    }

}

Registrar DB Command Interceptor (global.asax)

    DbInterception.Add(new OptionHintDbCommandInterceptor());

Habilitar todo el contexto

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

Activar o desactivar

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

Llamé a este HintWithRecompile, porque es posible que también desee implementar HintOptimizeForUnknown u otras sugerencias de consulta.

Answer 4

Lo mismo para mí que para @Greg, habilitar este sistema no era una opción, así que escribí esta pequeña clase de utilidad que puede agregar temporalmente la opción (recompilar) a consultas ejecutadas dentro de un OptionRecompileScope.

Ejemplo de uso

using (new OptionRecompileScope(dbContext))
{
    return dbContext.YourEntities.Where(<YourExpression>).ToList();
}

Implementación

public class OptionRecompileScope : IDisposable
{
    private readonly OptionRecompileDbCommandInterceptor interceptor;

    public OptionRecompileScope(DbContext context)
    {
        interceptor = new OptionRecompileDbCommandInterceptor(context);
        DbInterception.Add(interceptor);
    }

    public void Dispose()
    {
        DbInterception.Remove(interceptor);
    }

    private class OptionRecompileDbCommandInterceptor : IDbCommandInterceptor
    {
        private readonly DbContext dbContext;

        internal OptionRecompileDbCommandInterceptor(DbContext dbContext)
        {
            this.dbContext = dbContext;
        }

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

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

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

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

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

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

        private static void AddOptionRecompile(IDbCommand command)
        {
            command.CommandText = command.CommandText + " option(recompile)";
        }

        private bool ShouldIntercept(IDbCommand command, DbCommandInterceptionContext interceptionContext)
        {
            return 
                command.CommandType == CommandType.Text &&
                command is SqlCommand &&
                interceptionContext.DbContexts.Any(interceptionDbContext => ReferenceEquals(interceptionDbContext, dbContext));
        }
    }
}