entity framework - que - Agregar una sugerencia de consulta al llamar a la función de valores de tabla

sql 2017 mejoras

Question

Estoy llamando a una función de valor de tabla desde el marco de la entidad y necesito poder agregarle la option (recompile) porque el plan de ejecución que recoge no es óptimo. Al ejecutar la consulta en SQL Server Management Studio, se vería algo como esto:

select 
       * 
from dbo.fDE_myquery(0, 0, 3309, ''7/1/2013'', ''7/1/2014'', 0, 0)
option (recompile)

Por parte de EF, no hay manera de agregar esa sugerencia, AFAIK. La parte EF se ve algo así como:

var query = from f in ctx.fDE_myQuery(aBool, anotherBool, StartDate, 
            EndDate, someInt, moreBool)
            select f;

Vi esta pregunta:

¿Cómo controlo el rastreo de parámetros y / o las sugerencias de consulta en el marco de la entidad?

Pero es antigua, y la solución aceptada en realidad no brinda suficiente información sobre cómo implementar realmente la solución sugerida (usar guías de planes) con el marco de la entidad. Si esa es la única solución, ¿cómo consigue que el marco de la entidad utilice una guía de plan de todos modos?

Answer 1

¿Hay otros llamadores de fDE_myquery fuera de su uso específico? ¿Y con qué frecuencia se llama a esto? El problema no es que SELECT * FROM dbo.fDE_myquery(); está obteniendo un plan subóptimo, es que una o más consultas dentro de fDE_myquery está obteniendo un plan subóptimo. Por lo tanto, simplemente puede agregar la OPTION(RECOMPILE) a una o más consultas dentro de ese TVF.

Si se llama mucho a este TVF, esto tendría un impacto negativo en el rendimiento. Es por eso que pregunté acerca de otros usos de este TVF: si este es el único uso de este TVF, o de lejos el principal, entonces podría valer la pena si los planes malos se recogen con frecuencia.

Pero si hay varias otras personas que llaman a este TVF que no están experimentando un problema, entonces colocar el RECOMPILE en el TVF podría no ser el camino a seguir. Aunque, en ese caso, podría crear un envoltorio TVF que encapsule la SELECT * FROM dbo.fDE_myquery() OPTION (RECOMPILE); . Esto parece ser una solución más flexible :). Tendría que ser un TVF de múltiples estados en lugar del mejor TVF en línea, ya que lo probé y el TVF en línea no parece apreciar la cláusula OPTION , pero el TVF de múltiples estados estaba bien con eso.

EDITAR:
O, si desea manejar esto únicamente en EF, simplemente podría emitir una solicitud de recompilación con una sola línea de código:

ctx.context.ExecuteStoreCommand("EXEC sp_recompile ''dbo.fDE_myquery'';");

Y luego haz tu:

var query = from f in ctx.fDE_myQuery(aBool, anotherBool, StartDate, 
            EndDate, someInt, moreBool)
            select f;

Answer 2

Me encontré con esto:

https://entityframework.codeplex.com/wikipage?title=Interception

Y parece que puedes hacer algo como esto:

public class HintInterceptor : DbCommandInterceptor
{
    public override void ReaderExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
    {
        command.CommandText += " option (recompile)";
        base.ReaderExecuting(command, interceptionContext);
    }
}

Y regístrelo así (lo hice en Application_Start of global.asax.cs ):

DbInterception.Add(new HintInterceptor());

Y te permitirá alterar el CommandText . El único problema es que ahora está adjunto para cada consulta del lector, lo que podría ser un problema, ya que algunos de ellos podrían verse afectados negativamente por esa sugerencia. Supongo que puedo hacer algo con el contexto para averiguar si la sugerencia es apropiada o no, o en el peor de los casos, podría examinar el texto de CommandText sí.

No parece la solución más elegante o de grano fino.

Edición : Desde el interceptorContext , puede obtener los DbContexts , así que DbContexts una interfaz que se parece a esto:

public interface IQueryHintContext
{
    string QueryHint { get; set; }
    bool ApplyHint { get; set; }
}

Y luego creó una clase que deriva de mi DbContext original (generado por EF) e implementa la interfaz anterior. Entonces cambié mi interceptor para verse así:

public class HintInterceptor : DbCommandInterceptor
{
    public override void ReaderExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
    {
        if (interceptionContext.DbContexts.Any(db => db is Dal.IQueryHintContext))
        {
            var ctx = interceptionContext.DbContexts.First(db => db is Dal.IQueryHintContext) as Dal.IQueryHintContext;
            if (ctx.ApplyHint)
            {
                command.CommandText += string.Format(" option ({0})", ctx.QueryHint);
            }
        }
        base.ReaderExecuting(command, interceptionContext);
    }
}

Ahora, para usarlo, creo un contexto usando mi clase derivada en lugar de la original, establezco QueryHint a lo que quiera que sea ( recompile en este caso) y establezco ApplyHint justo antes de ejecutar el comando y luego lo establezco en falso.

Para hacer todo esto un poco más autónomo, terminé definiendo una interfaz como esta:

public interface IQueryHintContext
{
    string QueryHint { get; set; }
    bool ApplyHint { get; set; }
}

Y extendí el contexto de mi base de datos como este (podría, por supuesto, simplemente usar una clase parcial para extender también la clase generada por EF):

public class MyEntities_Ext : MyEntities, IQueryHintContext
{
    public string QueryHint { get; set; }
    public bool ApplyHint { get; set; }
}

Y luego, para hacer que la parte de encendido y apagado fuera un poco más fácil de manejar, definí esto:

public class HintScope : IDisposable
{
    public IQueryHintContext Context { get; private set; }
    public void Dispose()
    {
        Context.ApplyHint = false;
    }

    public HintScope(IQueryHintContext context, string hint)
    {
        Context = context;
        Context.ApplyHint = true;
        Context.QueryHint = hint;
    }
}

Ahora para usarlo, puedo hacer esto:

using (var ctx = new MyEntities_Ext()) 
{
    // any code that didn''t need the query hint
    // ....
    // Now we want the query hint
    using (var qh = new HintScope(ctx, "recompile"))
    {
        // query that needs the recompile hint
    }
    // back to non-hint code
}

Esto puede ser un poco excesivo y podría desarrollarse aún más (por ejemplo, usar una enumeración para las sugerencias disponibles en lugar de una cadena, o crear una subclase de una consulta de recompile para no tener que especificar la recompile la cadena cada vez y arriesgarse a un error tipográfico), pero resolvió mi problema inmediato