sql-server - 调用表值函数时添加查询提示

Question

我正在从 Entity Framework 调用表值函数，并且需要能够向其中添加选项(重新编译)，因为它选择的执行计划不是最佳的。在 SQL Server Management Studio 中运行查询，它看起来像这样:

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

来自 EF，据我所知，无法添加该提示。 EF 部分看起来像:

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

我看到这个问题:

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

但它已经过时了，并且已接受的解决方案并没有真正提供足够的信息来说明如何使用 Entity Framework 实际实现建议的解决方案(使用计划指南)。如果这是唯一的解决方案，那么如何让 Entity Framework 使用计划指南？

Answer 1

我遇到了这个:

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

看来你可以做这样的事情:

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

并像这样注册它(我在global.asax.cs的Application_Start中完成):

DbInterception.Add(new HintInterceptor());

它可以让你改变CommandText。唯一的问题是它现在附加到每个读者查询，这可能是一个问题，因为其中一些可能会受到该提示的负面影响。我猜我可以根据上下文做一些事情来确定提示是否合适，或者更糟糕的情况是我可以检查 CommandText 本身。

看起来并不是最优雅或最细粒度的解决方案。

编辑:从interceptorContext中，您可以获取DbContexts，因此我定义了一个如下所示的接口(interface):

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

然后创建一个派生 self 原来的 DbContext(由 EF 生成)的类并实现上述接口(interface)。然后我将拦截器更改为如下所示:

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

现在要使用它，我使用派生类而不是原始类创建一个上下文，将 QueryHint 设置为我想要的任何内容(在本例中为重新编译)并在执行命令之前设置 ApplyHint ，然后将其设置回 false。

为了使这一切更加独立，我最终定义了一个这样的接口(interface):

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

并像这样扩展了我的数据库上下文(当然，您也可以使用部分类来扩展 EF 生成的类):

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

然后，为了使打开、关闭部分更容易处理，我定义了以下内容:

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

现在要使用它，我可以这样做:

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
}

这可能有点矫枉过正，可以进一步开发(例如，使用枚举代替字符串来获取可用提示 - 或者子类化重新编译查询提示，这样您就不需要指定字符串每次都重新编译并冒打字错误的风险)，但它解决了我眼前的问题。