Почему добавление ненужного 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.