Почему оператор Contains () настолько резко ухудшает производительность Entity Framework?

ОБНОВЛЕНИЕ 3: Согласно этому объявлению , это было рассмотрено командой EF в EF6 alpha 2.

ОБНОВЛЕНИЕ 2: Я создал предложение исправить эту проблему. Чтобы проголосовать за это, перейдите сюда .

Рассмотрим базу данных SQL с одной очень простой таблицей.

CREATE TABLE Main (Id INT PRIMARY KEY) 

Я заполняю таблицу 10 000 записей.

 WITH Numbers AS ( SELECT 1 AS Id UNION ALL SELECT Id + 1 AS Id FROM Numbers WHERE Id <= 10000 ) INSERT Main (Id) SELECT Id FROM Numbers OPTION (MAXRECURSION 0) 

Я создаю EF-модель для таблицы и запускаю следующий запрос в LINQPad (я использую режим «C # Statements», так что LINQPad автоматически не создает дамп).

 var rows = Main .ToArray(); 

Время выполнения составляет ~ 0,07 секунды. Теперь я добавляю оператор Contains и повторно запускаю запрос.

 var ids = Main.Select(a => a.Id).ToArray(); var rows = Main .Where (a => ids.Contains(a.Id)) .ToArray(); 

Время выполнения для этого случая составляет 20.14 секунды (в 288 раз медленнее)!

Сначала я подозревал, что T-SQL, испущенный для запроса, занимал больше времени, поэтому я попытался вырезать и вставить его из панели SQL LINQPad в SQL Server Management Studio.

 SET NOCOUNT ON SET STATISTICS TIME ON SELECT [Extent1].[Id] AS [Id] FROM [dbo].[Primary] AS [Extent1] WHERE [Extent1].[Id] IN (1,2,3,4,5,6,7,8,... 

И результат был

 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 88 ms. 

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

Таким образом, похоже, что проблема находится где-то внутри Entity Framework.

Я здесь что-то не так? Это критически важная часть моего кода, так что я могу сделать, чтобы ускорить работу?

Я использую Entity Framework 4.1 и Sql Server 2008 R2.

ОБНОВЛЕНИЕ 1:

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

 var ids = Main.Select(a => a.Id).ToArray(); var rows = (ObjectQuery) Main .Where (a => ids.Contains(a.Id)); var sql = rows.ToTraceString(); 

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

CompiledQuery на помощь тогда? Не так быстро … CompiledQuery требует, чтобы параметры, переданные в запрос, были фундаментальными (int, string, float и т. Д.). Он не принимает массивы или IEnumerable, поэтому я не могу использовать его для списка идентификаторов.

ОБНОВЛЕНИЕ: с добавлением InExpression в EF6 производительность обработки Enumerable.Contains значительно улучшилась. Подход, описанный в этом ответе, больше не нужен.

Вы правы, что большую часть времени тратится на обработку перевода запроса. Модель поставщика EF в настоящее время не включает выражение, которое представляет предложение IN, поэтому поставщики ADO.NET не могут поддерживать IN изначально. Вместо этого реализация Enumerable.Contains переводит его в дерево выражений OR, то есть для того, что в C # выглядит следующим образом:

 new []{1, 2, 3, 4}.Contains(i) 

… мы сгенерируем дерево DbExpression, которое может быть представлено следующим образом:

 ((1 = @i) OR (2 = @i)) OR ((3 = @i) OR (4 = @i)) 

(Деревья выражений должны быть сбалансированы, потому что, если бы у нас были все ORs по одному длинному позвоночнику, было бы больше шансов, что посетитель выражения попадет в переполнение стека (да, мы действительно нанесли удар в нашем тестировании))

Позже мы отправим такое дерево поставщику ADO.NET, который может распознать этот шаблон и свести его к предложению IN во время генерации SQL.

Когда мы добавили поддержку Enumerable.Contains в EF4, мы думали, что было бы желательно сделать это без необходимости вводить поддержку выражений IN в модели поставщика, и, честно говоря, 10 000 намного больше, чем количество ожидаемых нами клиентов Enumerable.Contains. Тем не менее, я понимаю, что это раздражение и что манипуляция деревьями выражений делает вещи слишком дорогими в вашем конкретном сценарии.

Я обсуждал это с одним из наших разработчиков, и мы полагаем, что в будущем мы сможем изменить реализацию, добавив первоclassную поддержку IN. Я буду уверен, что это добавлено к нашему отставанию, но я не могу обещать, когда это будет сделано, поскольку есть много других улучшений, которые мы хотели бы сделать.

К обходным решениям, уже предложенным в streamе, я бы добавил следующее:

Подумайте о создании метода, который уравновешивает количество обращений к базам данных с количеством элементов, которые вы передаете в Содержит. Например, в моем собственном тестировании я заметил, что вычисление и выполнение против локального экземпляра SQL Server запроса с 100 элементами занимает 1/60 секунды. Если вы можете написать свой запрос таким образом, что выполнение 100 запросов с 100 различными наборами идентификаторов даст вам эквивалентный результат для запроса с 10 000 элементов, вы можете получить результаты примерно за 1,67 секунды вместо 18 секунд.

Различные размеры блоков должны работать лучше в зависимости от запроса и латентности подключения к базе данных. Для определенных запросов, т. Е. Если прошедшая последовательность имеет дубликаты или если Enumerable.Contains используется в вложенном состоянии, вы можете получить дубликаты элементов в результатах.

Вот fragment кода (извините, если код, используемый для fragmentа ввода в куски, выглядит слишком сложным. Есть более простые способы достижения одного и того же, но я пытался создать шаблон, который сохраняет stream для последовательности и Я не мог найти ничего подобного в LINQ, поэтому я, вероятно, переусердствовал с этой частью :)):

Применение:

 var list = context.GetMainItems(ids).ToList(); 

Метод для контекста или репозитория:

 public partial class ContainsTestEntities { public IEnumerable
GetMainItems(IEnumerable ids, int chunkSize = 100) { foreach (var chunk in ids.Chunk(chunkSize)) { var q = this.MainItems.Where(a => chunk.Contains(a.Id)); foreach (var item in q) { yield return item; } } } }

Методы расширения для нарезки перечислимых последовательностей:

 public static class EnumerableSlicing { private class Status { public bool EndOfSequence; } private static IEnumerable TakeOnEnumerator(IEnumerator enumerator, int count, Status status) { while (--count > 0 && (enumerator.MoveNext() || !(status.EndOfSequence = true))) { yield return enumerator.Current; } } public static IEnumerable> Chunk(this IEnumerable items, int chunkSize) { if (chunkSize < 1) { throw new ArgumentException("Chunks should not be smaller than 1 element"); } var status = new Status { EndOfSequence = false }; using (var enumerator = items.GetEnumerator()) { while (!status.EndOfSequence) { yield return TakeOnEnumerator(enumerator, chunkSize, status); } } } } 

Надеюсь это поможет!

Если вы обнаружите, что проблема с производительностью, которая блокирует вас, вы не пытаетесь потратить время на ее решение, потому что вы, скорее всего, не добьетесь успеха, и вам придется напрямую общаться с MS (если у вас есть премиальная поддержка), и требуется возрастов.

Используйте обходной путь и обходной путь в случае проблемы с производительностью, а EF – прямой SQL. В этом нет ничего плохого. Глобальная идея, что использование EF = не использование SQL больше – ложь. У вас есть SQL Server 2008 R2, поэтому:

  • Создайте хранимую процедуру, принимающую параметр таблицы, чтобы передать ваши идентификаторы
  • Пусть ваша хранимая процедура возвращает несколько наборов результатов для оптимального эмуляции Include logic
  • Если вам нужно какое-то сложное построение запросов, используйте динамический SQL внутри хранимой процедуры
  • Используйте SqlDataReader для получения результатов и построения своих объектов
  • Прикрепите их к контексту и работайте с ними, как если бы они были загружены из EF

Если производительность важна для вас, вы не найдете лучшего решения. Эта процедура не может быть сопоставлена ​​и выполнена EF, поскольку текущая версия не поддерживает ни табличные параметры, ни несколько наборов результатов.

Мы смогли решить проблему EF Contains, добавив промежуточную таблицу и присоединившись к этой таблице из запроса LINQ, который должен был использовать предложение Contains. При таком подходе мы смогли получить потрясающие результаты. У нас есть большая EF-модель, и поскольку «Содержит» не разрешается при предварительном компиляции запросов EF, мы получаем очень низкую производительность для запросов, которые используют предложение «Содержит».

Обзор:

  • Создайте таблицу в SQL Server – например, HelperForContainsOfIntType с HelperID типа данных HelperID of Guid и ReferenceID столбцов типа int . При необходимости создайте различные таблицы с помощью ReferenceID разных типов данных.

  • Создайте Entity / EntitySet для HelperForContainsOfIntType и другие такие таблицы в EF-модели. При необходимости создайте различные Entity / EntitySet для разных типов данных.

  • Создайте вспомогательный метод в .NET-коде, который принимает вход IEnumerable и возвращает Guid . Этот метод генерирует новый Guid и вставляет значения из IEnumerable в HelperForContainsOfIntType вместе с созданным Guid . Затем метод возвращает этот вновь созданный Guid для вызывающего. Для быстрой вставки в таблицу HelperForContainsOfIntType создайте хранимую процедуру, которая принимает ввод списка значений и выполняет вставку. См. Табличные параметры в SQL Server 2008 (ADO.NET) . Создайте разные помощники для разных типов данных или создайте общий вспомогательный метод для обработки разных типов данных.

  • Создайте скомпилированный запрос EF, который похож на что-то вроде ниже:

     static Func> _selectCustomers = CompiledQuery.Compile( (MyEntities db, Guid containsHelperID) => from cust in db.Customers join x in db.HelperForContainsOfIntType on cust.CustomerID equals x.ReferenceID where x.HelperID == containsHelperID select cust ); 
  • Вызовите метод helper со значениями, которые будут использоваться в предложении Contains , и получите Guid для использования в запросе. Например:

     var containsHelperID = dbHelper.InsertIntoHelperForContainsOfIntType(new int[] { 1, 2, 3 }); var result = _selectCustomers(_dbContext, containsHelperID).ToList(); 

Редактирование моего первоначального ответа. Существует возможное обходное решение, в зависимости от сложности ваших объектов. Если вы знаете sql, который EF генерирует для заполнения ваших объектов, вы можете выполнить его напрямую, используя DbContext.Database.SqlQuery . В EF 4, я думаю, вы могли бы использовать ObjectContext.ExecuteStoreQuery , но я не пробовал.

Например, используя код из моего первоначального ответа ниже, чтобы сгенерировать оператор sql с помощью StringBuilder , я смог сделать следующее

 var rows = db.Database.SqlQuery
(sql).ToArray();

и общее время составляло от 26 секунд до 0,5 секунды.

Я буду первым, кто скажет, что это уродливо, и, надеюсь, лучшее решение представлено.

Обновить

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

Чтобы проверить это, я создал таблицу Target с той же схемой, что и Main . Затем я использовал StringBuilder для создания команд INSERT чтобы заполнить таблицу Target партиями из 1000, так как большинство SQL Server будет принимать в одном INSERT . Прямо выполнение операторов sql было намного быстрее, чем через EF (примерно 0,3 секунды против 2,5 секунд), и я считаю, что это нормально, поскольку схема таблицы не должна меняться.

Наконец, выбор с использованием join привел к значительно более простому запросу и выполнялся менее чем за 0,5 секунды.

 ExecuteStoreCommand("DELETE Target"); var ids = Main.Select(a => a.Id).ToArray(); var sb = new StringBuilder(); for (int i = 0; i < 10; i++) { sb.Append("INSERT INTO Target(Id) VALUES ("); for (int j = 1; j <= 1000; j++) { if (j > 1) { sb.Append(",("); } sb.Append(i * 1000 + j); sb.Append(")"); } ExecuteStoreCommand(sb.ToString()); sb.Clear(); } var rows = (from m in Main join t in Target on m.Id equals t.Id select m).ToArray(); rows.Length.Dump(); 

И sql, сгенерированный EF для соединения:

 SELECT [Extent1].[Id] AS [Id] FROM [dbo].[Main] AS [Extent1] INNER JOIN [dbo].[Target] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id] 

(оригинальный ответ)

Это не ответ, но я хотел бы поделиться некоторой дополнительной информацией, и слишком долго вписываться в комментарий. Я смог воспроизвести ваши результаты и добавить еще несколько вещей:

SQL Profiler показывает задержку между выполнением первого запроса ( Main.Select ) и второго запроса Main.Where , поэтому я подозревал, что проблема заключается в генерации и отправке запроса такого размера (48,980 байт).

Тем не менее, создание одного и того же оператора sql в T-SQL динамически занимает менее 1 секунды, и принятие ids из вашего оператора Main.Select , построение одного и того же оператора sql и выполнение его с использованием SqlCommand занимает 0.112 секунды, и это включает время для записи содержимое консоли.

На этом этапе я подозреваю, что EF выполняет некоторый анализ / обработку для каждого из 10 000 ids поскольку он строит запрос. Хотел бы я дать окончательный ответ и решение :(.

Вот код, который я пробовал в SSMS и LINQPad (пожалуйста, не критикуйте слишком жестко, я спешу, пытаясь оставить работу):

 declare @sql nvarchar(max) set @sql = 'SELECT [Extent1].[Id] AS [Id] FROM [dbo].[Main] AS [Extent1] WHERE [Extent1].[Id] IN (' declare @count int = 0 while @count < 10000 begin if @count > 0 set @sql = @sql + ',' set @count = @count + 1 set @sql = @sql + cast(@count as nvarchar) end set @sql = @sql + ')' exec(@sql) 

 var ids = Mains.Select(a => a.Id).ToArray(); var sb = new StringBuilder(); sb.Append("SELECT [Extent1].[Id] AS [Id] FROM [dbo].[Main] AS [Extent1] WHERE [Extent1].[Id] IN ("); for(int i = 0; i < ids.Length; i++) { if (i > 0) sb.Append(","); sb.Append(ids[i].ToString()); } sb.Append(")"); using (SqlConnection connection = new SqlConnection("server = localhost;database = Test;integrated security = true")) using (SqlCommand command = connection.CreateCommand()) { command.CommandText = sb.ToString(); connection.Open(); using(SqlDataReader reader = command.ExecuteReader()) { while(reader.Read()) { Console.WriteLine(reader.GetInt32(0)); } } } в var ids = Mains.Select(a => a.Id).ToArray(); var sb = new StringBuilder(); sb.Append("SELECT [Extent1].[Id] AS [Id] FROM [dbo].[Main] AS [Extent1] WHERE [Extent1].[Id] IN ("); for(int i = 0; i < ids.Length; i++) { if (i > 0) sb.Append(","); sb.Append(ids[i].ToString()); } sb.Append(")"); using (SqlConnection connection = new SqlConnection("server = localhost;database = Test;integrated security = true")) using (SqlCommand command = connection.CreateCommand()) { command.CommandText = sb.ToString(); connection.Open(); using(SqlDataReader reader = command.ExecuteReader()) { while(reader.Read()) { Console.WriteLine(reader.GetInt32(0)); } } } 

Я не знаком с Entity Framework, но лучше ли работать, если вы делаете следующее?

Вместо этого:

 var ids = Main.Select(a => a.Id).ToArray(); var rows = Main.Where (a => ids.Contains(a.Id)).ToArray(); 

как об этом (при условии, что ID является int):

 var ids = new HashSet(Main.Select(a => a.Id)); var rows = Main.Where (a => ids.Contains(a.Id)).ToArray(); 

Кэшируемая альтернатива Содержит?

Это просто укусило меня, поэтому я добавил два пенса в ссылку Feature Feature Feature.

Проблема при создании SQL. У меня есть клиент, у кого есть данные, генерация запросов составляла 4 секунды, но выполнение составляло 0,1 секунды.

Я заметил, что при использовании динамических LINQ и OR генерация sql длилась так же долго, но она генерировала то, что можно было кэшировать . Поэтому при повторном запуске это было всего 0,2 секунды.

Обратите внимание, что SQL-запрос все еще сгенерирован.

Просто что-то еще, чтобы рассмотреть, можете ли вы перенести первоначальный удар, количество ваших массивов не сильно изменится и много раз запустит запрос. (Протестировано в LINQ Pad)

Проблема заключается в генерации SQL Server Entity Framework. Он не может кэшировать запрос, если один из параметров – это список.

Чтобы заставить EF кэшировать ваш запрос, вы можете преобразовать свой список в строку и сделать .Contains в строке.

Так, например, этот код будет работать намного быстрее, поскольку EF может кэшировать запрос:

 var ids = Main.Select(a => a.Id).ToArray(); var idsString = "|" + String.Join("|", ids) + "|"; var rows = Main .Where (a => idsString.Contains("|" + a.Id + "|")) .ToArray(); 

Когда этот запрос сгенерирован, он скорее всего будет сгенерирован с использованием Like, а не In, поэтому он ускорит ваш C #, но может потенциально замедлить работу вашего SQL. В моем случае я не заметил снижения производительности в моем SQL-исполнении, а C # выполнялся значительно быстрее.

Interesting Posts

Надстройка для блокировки сайтов в Поиске Google

Как выбрать DTD и XSD

Загрузка нескольких изображений в MATLAB

Сохранение папки локальных окон в синхронизации с удаленной ftp-папкой в ​​режиме реального времени

Самый эффективный способ изменения размера растровых изображений на Android?

Перегрев ноутбука, BSOD, отключается при игре в игры

Кнопка Аккордеон Bootstrap для переключения “data-parent” не работает

Как выбрать строки из трехмерного тензора в TensorFlow?

Установите фокус на текстовое поле в WPF из модели просмотра (C #)

Заполнение отсутствующих значений по группам в data.table

Проект Visual Studio 2012 Web API не запускается – не удается найти Newtonsoft.Json

Что такое одно правило определения в C ++?

Почему я не должен использовать «венгерскую нотацию»?

Редактирование / etc / hosts для использования имени домена вместо ip-адреса

Проводник Windows. Как большой файл может иметь значение «Размер на диске»? Что это значит

Давайте будем гением компьютера.