Почему добавление ненужного ToList() резко ускоряет этот запрос LINQ?

Почему принудительное использование материализации с помощью ToList() делает мои запросы на порядок быстрее, если что-либо, оно должно делать абсолютно противоположное?

1) Вызов First() немедленно

    // "Context" is an Entity Framework DB-first model

    var query = from x in Context.Users
                where x.Username.ToLower().Equals(User.Identity.Name.ToLower())
                select x;

    var User = query.First();

    //  ** The above takes 30+ seconds to run **

2) Вызов First() после вызова ToList():

    var query = from x in Context.Users
                where x.Username.ToLower().Equals(User.Identity.Name.ToLower())
                select x;

    var User = query.ToList().First();     // Added ToList() before First()

    // ** Now it takes < 1 second to run! **

Обновление и разрешение

После получения сгенерированного SQL единственная разница, как и ожидалось, добавляет TOP (1) в первый запрос. Как говорит Andyz Smith в своем ответе ниже, основной причиной является то, что оптимизатор SQL Server в этом конкретном случае выбирает худший план выполнения при добавлении TOP (1). Таким образом, проблема не имеет ничего общего с LINQ (что было правильно, добавив TOP (1)) и все, что связано с особенностями SQL Server.

Ответы

Ответ 1

Итак, оптимизатор выбирает плохой способ выполнить запрос.

Поскольку вы не можете добавить подсказки оптимизатора к SQL, чтобы заставить оптимизатор выбрать лучший план, я вижу два варианта.

  • Добавить индекс покрытия/индексный вид во всех столбцах, которые извлекаются/включаются в select. Довольно смехотворно, но я думаю, что это сработает, потому что этот индекс облегчит работу для оптимизатора выбрать лучший план.

  • Всегда преждевременно материализуйте запросы, которые включают First или Last или Take. Опасно, потому что, поскольку данные становятся больше, точка безубыточности между удалением всех данных локально и выполнением First() и выполнением запроса с Top на сервере будет изменяться.

http://geekswithblogs.net/Martinez/archive/2013/01/30/why-sql-top-may-slow-down-your-query-and-how.aspx

https://groups.google.com/forum/m/#!topic/microsoft.public.sqlserver.server/L2USxkyV1uw

http://connect.microsoft.com/SQLServer/feedback/details/781990/top-1-is-not-considered-as-a-factor-for-query-optimization

TOP замедляет запрос

Почему TOP или SET ROWCOUNT делают мой запрос настолько медленным?

Ответ 2

Я могу думать только об одной причине... Чтобы проверить его, можете ли вы удалить предложение Where и повторно запустить тест? Прокомментируйте здесь, если в результате первое выражение будет быстрее, и я объясню, почему.

Изменить
В предложение LINQ statement Where вы используете метод .ToLower() для строки. Я предполагаю, что LINQ не имеет встроенного преобразования в SQL для этого метода, поэтому результирующий SQL-это строка

SELECT *
FROM Users

Теперь мы знаем, что LINQ lazy load, но он также знает, что, поскольку он не оценил предложение Where, ему нужно загрузить элементы для сравнения.

Гипотезы
Первый запрос - это ленивая загрузка КАЖДЫЙ элемент в результирующем наборе. Затем он выполняет сравнение .ToLower() и возвращает первый результат. Это приводит к запросам n к серверу и огромным служебным нагрузкам. Не уверен, не видя SQL Tracelog.

Второй оператор вызывает ToList, который запрашивает пакетный SQL перед выполнением сравнения ToLower, в результате чего на сервер приходит только один запрос

Альтернативная гипотеза
Если профайлер показывает только одно выполнение сервера, попробуйте выполнить тот же запрос с предложением Top 1 и посмотреть, требуется ли оно так долго. В соответствии с этим сообщением (Почему верхняя часть (1) в индексированном столбце SQL Server медленна?) предложение TOP иногда может испортиться с оптимизатором SQL-сервера и остановить он использует правильные индексы.

Редактирование любопытства
попробуйте изменить LINQ на

var query = from x in Context.Users
            where x.Username.Equals(User.Identity.Name, StringComparison.OrdinalIgnoreCase)
            select x;

Кредит @Scott для поиска способа делать нечувствительное к регистру сравнение в LINQ. Дайте ему понять, быстрее ли это.

Ответ 3

SQL не будет таким же, как Linq ленивая загрузка. Таким образом, ваш вызов .ToList() заставит .NET оценивать выражение, затем в памяти выберите элемент first().

Где в качестве другой опции следует добавить top 1 в SQL

например.

var query = from x in Context.Users
                where x.Username.ToLower().Equals(User.Identity.Name.ToLower())
                select x;

 //SQL executed here
 var User = query.First();

и

var query = from x in Context.Users
                where x.Username.ToLower().Equals(User.Identity.Name.ToLower())
                select x;

 //SQL executed here!
 var list = query.ToList();
 var User = query.First();

Как ниже, первый запрос должен быть быстрее! Я бы предложил сделать SQL-профайлер, чтобы узнать, что происходит. Скорость запросов будет зависеть от вашей структуры данных, количества записей, индексов и т.д.

Время вашего теста также изменит результаты. Как отметили в комментарии несколько человек, в первый раз, когда вы попали в EF, ему нужно инициализировать и загружать метаданные. поэтому, если вы запускаете их вместе, первый должен всегда быть медленным.

Вот еще информация об EF соображения производительности

обратите внимание на строку:

Метаданные модели и отображения, используемые платформой Entity Framework, загружаются в метаданных. Эти метаданные кэшируются глобально и доступны к другим экземплярам ObjectContext в том же домене приложения.

&

Поскольку открытое соединение с базой данных потребляет ценную ресурс, Entity Framework открывает и закрывает базу данных соединение только по мере необходимости. Вы также можете явно открыть подключение. Дополнительные сведения см. В разделе Управление подключениями и Транзакции в платформе Entity Framework.