Значительная разница в производительности между Entity Framework, сгенерированной sp_executesql и прямым запросом в SSMS

Вопрос:

Я использую Entity Framework для создания довольно большого запроса. В последнее время этот запрос терпит неудачу из-за исключений таймаута.

Когда я начал исследовать эту проблему, я использовал LinqPad и напрямую скопировал вывод SQL в SSMS и выполнил запрос. Этот запрос возвращается в течение 1 секунды!

Затем запрос выглядит (только для иллюстрации, реальный запрос намного больше)

DECLARE @p__linq__0 DateTime2 = '2017-10-01 00:00:00.0000000'
DECLARE @p__linq__1 DateTime2 = '2017-10-31 00:00:00.0000000'

SELECT
[Project8].[Volgnummer] AS [Volgnummer],
[Project8].[FkKlant] AS [FkKlant],
-- rest omitted for brevity

Теперь я использовал SQL Profiler для захвата реальной отправки SQL на сервер. Запрос в точности совпадает с тем отличием, что этот запрос инкапсулируется в вызове sp_executesql. Как это:

exec sp_executesql N'SELECT
[Project8].[Volgnummer] AS [Volgnummer],
[Project8].[FkKlant] AS [FkKlant],
-- rest omitted for brevity
',N'@p__linq__0 datetime2(7),@p__linq__1 datetime2(7)',
@p__linq__0='2017-10-01 00:00:00',@p__linq__1='2017-10-31 00:00:00'

Когда я копирую/вставляю этот запрос в SSMS, он работает в течение 60 секунд и, таким образом, приводит к таймауту при использовании из EF с настройками по умолчанию!

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

Я много читал о том, почему EF использует sp_executesql, и я понимаю, почему. Я также читал, что sp_executesql отличается от EXEC, потому что он использует кеш queryplan, но я не понимаю, почему оптимизатор SQL имеет такую трудность в создании плана выполнения результатов для версии sp_executesql, тогда как он способен создать исполняемый запрос planplan для прямой версии запроса.

Я не уверен, что сам вопрос добавляет к вопросу. Если да, дайте мне знать, и я сделаю редактирование.

Лучший ответ:

Благодаря предоставленным комментариям мне удалось сделать две вещи:

  • Теперь я понимаю план запроса и различия между параметризацией и переменными в запросах
  • Я внедрил DbCommandInterceptor чтобы добавить OPTION (OPTIMIZE FOR UNKNOWN) к запросу, когда это необходимо.

SQL-запрос, скомпилированный Entity Framework, может быть перехвачен перед отправкой на сервер путем добавления реализации к DbInterception.

Такая реализация тривиальна:

public class QueryHintInterceptor : DbCommandInterceptor
{
    public override void ReaderExecuting(DbCommand command, 
        DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        queryHint = " OPTION (OPTIMIZE FOR UNKNOWN)";
        if (!command.CommandText.EndsWith(queryHint))
        {
            command.CommandText += queryHint;
        }

        base.ReaderExecuting(command, interceptionContext);
    }
}
// Add to the interception proces:
DbInterception.Add(new QueryHintsInterceptor());

Поскольку Entity Framework также кэширует запросы, я проверяю, была ли добавлена оптимизация.

Но этот подход перехватит все запросы, и, очевидно, этого не следует делать. Поскольку DbCommandInterceptionContext предоставляет доступ к DbContext я добавил интерфейс с единственным свойством (ISupportQueryHints) в свой DbContext который я настроил на оптимизацию, когда это необходимо запросу.

Теперь это выглядит так:

 public class QueryHintInterceptor : DbCommandInterceptor
{
    public override void ReaderExecuting(DbCommand command, 
        DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        var dbContext =
            interceptionContext.DbContexts.FirstOrDefault(d => d is ISupportQueryHints) as ISupportQueryHints;

        if (dbContext != null)
        {
            var queryHint = $" OPTION ({dbContext.QueryHint})";
            if (!command.CommandText.EndsWith(queryHint))
            {
                command.CommandText += queryHint;
            }
        }

        base.ReaderExecuting(command, interceptionContext);
    }
}

При необходимости это может использоваться как:

public IEnumerable<SomeDto> QuerySomeDto()
{
    using (var dbContext = new MyQuerySupportingDbContext())
    {
        dbContext.QueryHint = "OPTIMIZE FOR UNKNOWN";
        return this.PerformQuery(dbContext);
    }
}

Поскольку мое приложение использует архитектуру, основанную на сообщениях, вокруг команд и запросов, как описано здесь, моя реализация состоит из декоратора вокруг обработчиков запросов, нуждающихся в оптимизации. Этот декоратор задает подсказки для запроса в DbContext всякий раз, когда это необходимо. Это, однако, деталь реализации. Основная идея остается прежней.

Оцените статью
TechArks.Ru
Добавить комментарий