EF 6参数嗅探

本文关键字：嗅探 参数 EF | 更新日期: 2023-09-27 18:16:26

我有一个动态查询，它太大了，不能放在这里。可以肯定地说，在它当前的形式中，它利用CLR过程根据传递的搜索参数的数量动态构建连接，然后获取该结果并将其连接到更详细的表中，以带回对最终用户重要的属性。我已经将整个查询转换为LINQ到实体，我发现它产生的SQL足够有效，可以完成这项工作，但是通过EF 6运行，查询超时。获取结果SQL并在SSMS中运行它只需3秒或更短的时间。我只能想象我的问题是参数嗅探。我已经尝试更新数据库中每个表的统计信息，但这并没有解决问题。

My Question is:

我可以通过EF嵌入像"选项重新编译"这样的选项吗?

EF 6参数嗅探

可以使用EF6的拦截功能来操作其内部SQL命令，然后在DB上执行它们，例如在命令末尾添加option(recompile):

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

要使用它，请在应用程序的开头添加以下行:

DbInterception.Add(new OptionRecompileHintDbCommandInterceptor());

我喜欢VahidN的解决方案，投票给他，但是当发生时，我想要更多的控制。事实证明，DB拦截器是非常全局的，我只希望它发生在特定场景的特定上下文中。

这里我们正在做基础工作，以支持添加其他查询提示，这些提示可以根据需要打开或关闭。

由于我经常公开传递连接字符串的方法，因此我也包含了对该方法的支持。

下面的

将通过扩展EF生成的部分类，为您的上下文提供一个标记，以编程方式启用/禁用提示。我们还将拦截器中重用的一小段代码扔到它自己的方法中。

小界面

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

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

扩展实体上下文以添加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());

启用上下文宽度

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

打开或关闭

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

我把这个叫做HintWithRecompile，因为你可能还想实现一个HintOptimizeForUnknown，或者其他查询提示。

对于我来说和@Greg一样，启用这个系统范围不是一个选项，所以我编写了这个小实用程序类，可以临时添加选项(重新编译)到OptionRecompileScope中执行的查询。

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

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 += " option(recompile)";
        }
        private bool ShouldIntercept(IDbCommand command, DbCommandInterceptionContext interceptionContext)
        {
            return 
                command.CommandType == CommandType.Text &&
                command is SqlCommand &&
                interceptionContext.DbContexts.Any(interceptionDbContext => ReferenceEquals(interceptionDbContext, dbContext));
        }
    }
}

我有一个类似的问题。最后，我使用以下命令删除了缓存的查询计划:

dbcc freeproccache([your plan handle here])

为了得到你的计划句柄，你可以使用下面的查询:

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%'

用适当的查询片段替换"喜欢"子句的内容。

你可以看到我的整个问题:

使用实体框架的SQL查询运行速度较慢，使用错误的查询计划

在EF Core 2中也有类似的情况，但它只是在Interceptor实现中有所不同。既然这个帖子对我帮助最大，我想和你分享我的实现，即使OP要求EF 6。此外，我还改进了@Oskar Sjöberg和@Greg Solution，以挑选出应该使用recompile选项扩展的查询。

在EF Core 2中，拦截器有点棘手，有点不同。

可以通过Microsoft.Extensions.DiagnosticAdapter包和下面的代码

实现
var contextDblistener = this.contextDb.GetService<DiagnosticSource>();
(contextDblistener as DiagnosticListener).SubscribeWithAdapter(new SqlCommandListener());

拦截器本身需要用相应的DiagnosticName注释标记它的方法。

我给拦截器的调整是，它在命令中寻找特定的标签(sql注释)，以挑出应该用所需选项扩展的查询。

要将查询标记为使用重新编译选项，您只需在查询中添加一个.TagWith(Constants.SQL_TAG_QUERYHINT_RECOMPILE)，而不必费心将bool设置为true并返回false。

这样，你也不会有并行查询被截获的问题，因为一个单一的bool HintWithRecompile，所有的查询都被扩展为一个重新编译选项。

常量标记字符串的设计使它们只能在sql注释中，而不是查询本身的一部分。我找不到一个解决方案来分析标记部分(EF的实现细节)，所以整个sql命令被分析，你不想添加一个重新编译，因为查询中的一些文本匹配你的标志。

"优化未知"部分可以通过使用命令参数属性进一步改进，但我将把它留给你。

public class SqlCommandListener
{
    [DiagnosticName("Microsoft.EntityFrameworkCore.Database.Command.CommandExecuting")]
    public void OnCommandExecuting(DbCommand command, DbCommandMethod executeMethod, Guid commandId, Guid connectionId, bool async, DateTimeOffset startTime)
    {
        AddQueryHintsBasedOnTags(command);
    }
    [DiagnosticName("Microsoft.EntityFrameworkCore.Database.Command.CommandExecuted")]
    public void OnCommandExecuted(object result, bool async)
    {
    }
    [DiagnosticName("Microsoft.EntityFrameworkCore.Database.Command.CommandError")]
    public void OnCommandError(Exception exception, bool async)
    {
    }
    private static void AddQueryHintsBasedOnTags(DbCommand command)
    {
        if (command.CommandType != CommandType.Text || !(command is SqlCommand))
        {
            return;
        }
        if (command.CommandText.Contains(Constants.SQL_TAG_QUERYHINT_RECOMPILE) && !command.CommandText.Contains("OPTION (RECOMPILE)", StringComparison.InvariantCultureIgnoreCase))
        {
            command.CommandText = command.CommandText + "'nOPTION (RECOMPILE)";
        }
        else if (command.CommandText.Contains(Constants.SQL_TAG_QUERYHINT_OPTIMIZE_UNKNOWN_USER) && !command.CommandText.Contains("OPTION (OPTIMIZE FOR (@__SomeUserParam_0 UNKNOWN))", StringComparison.InvariantCultureIgnoreCase))
        {
            command.CommandText = command.CommandText + "'nOPTION (OPTIMIZE FOR (@__SomeUserParam_0 UNKNOWN))";
        }
    }
}

Edit:订阅DiagnosticSource时要小心，因为它是而不是订阅上下文对象。诊断源具有另一个生存期(并且可以是许多上下文的源)。因此，如果您订阅所创建的每个作用域上下文，您最终将创建越来越多的订阅。关于只创建一个订阅的解决方案，请参阅我的答案。

相关文章：