EF 6 Параметр Sniffing

У меня есть динамический запрос, который слишком велик для ввода здесь. Безопасно сказать, что в текущей форме он использует процедуру CLR для динамического построения соединений на основе количества переданных параметров поиска, затем берет этот результат и присоединяет его к более подробным таблицам, чтобы вернуть атрибуты, важные для конечного пользователя. Я преобразовал весь запрос в LINQ в Entities, и я обнаружил, что SQL, который он производит, достаточно эффективен для выполнения задания, но работает через EF 6, тайм-аут запроса. Взятие полученного SQL и запуск его в SSMS выполняется за 3 или менее секунды. Я могу только представить себе, что моя проблема - обнюхивание параметров. Я пробовал обновлять статистику по каждой таблице в базе данных, и это не решило проблему.

Мой вопрос:

Могу ли я каким-либо образом внедрить такие опции, как "ОПЦИИ РЕКОМПЛИКАЦИИ" через EF?

Ответы

Ответ 1

Можно использовать функцию перехвата EF6 для управления внутренними командами SQL перед выполнением их в БД, например добавление 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());

Ответ 2

Мне нравится решение VahidN, проголосуйте за него, но я хочу больше контролировать , когда это происходит. Оказывается, DB Interceptors очень глобальны, и я только хотел, чтобы это происходило в определенных контекстах в определенных сценариях.

Здесь мы настраиваем наземную работу, чтобы также поддерживать добавление других подсказок, которые можно было бы включать и выключать по желанию.

Поскольку я часто выставляю метод для передачи строки подключения, я также включил поддержку для этого.

Ниже будет ваш контекст флагом для включения/выключения подсказки программно, путем расширения частичного класса EF. Мы также бросили небольшую часть повторно используемого кода в Interceptor в свой собственный метод.

Малый интерфейс

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

Расширение контекста Entity для добавления 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)
    {
    }

}

Зарегистрировать перехватчик команд базы данных (global.asax)

    DbInterception.Add(new OptionHintDbCommandInterceptor());

Включить контекст широко

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

Включение и отключение

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

Я назвал этот HintWithRecompile, потому что вы также можете реализовать HintOptimizeForUnknown или другие подсказки запросов.

Ответ 3

Для меня так же, как и для @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 = 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));
        }
    }
}

Ответ 4

У меня была аналогичная проблема. В конце концов, я удалил план кеширования с помощью этой команды:

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

заменяя содержимое "like" предложения соответствующими фрагментами вашего запроса.

Вы можете увидеть мою проблему:

SQL-запрос с использованием Entity Framework работает медленнее, использует плохой план запроса

Ответ 5

Был аналогичный случай в EF Core 2, но он отличается только реализацией Interceptor. Так как эта ветка помогла мне больше всего, я хочу поделиться с вами своей реализацией, даже если ОП попросил EF 6. Кроме того, я немного улучшил @Oskar Sjöberg и @Greg Solution, чтобы выделить запросы, которые должны быть расширены с помощью опции перекомпиляции.

В EF Core 2 Interceptor немного сложнее и немного отличается.

Это можно реализовать через пакет 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))";
        }
    }
}