Глубокое погружение в производительность Entity Framework при использовании «содержит»

В своей повседневной работе я использую Entity Framework. Это очень удобно, но в некоторых случаях его работа замедляется. Несмотря на то, что существует множество хороших статей об улучшении производительности EF и даются очень хорошие и полезные советы (например, избегать сложных запросов, параметров в Skip and Take, использовать представления, выбирать только нужные поля и т. на самом деле это нужно делать, когда вам нужно использовать сложное Contains в двух или более полях — другими словами, когда вы присоединяете данные к списку памяти .

Проблема

Давайте проверим следующий пример:

 var localData = GetDataFromApiOrUser(); var query = from p in context.Prices join s in context.Securities on p.SecurityId equals s.SecurityId join t in localData on new { s.Ticker, p.TradedOn, p.PriceSourceId } equals new { t.Ticker, t.TradedOn, t.PriceSourceId } select p; var result = query.ToList();

Приведенный выше код вообще не работает в EF 6, и хотя он работает в EF Core, объединение фактически выполняется локально — поскольку в моей базе данных десять миллионов записей, все они загружаются, и вся память расходуется. . Это не ошибка в EF. Это ожидаемо. Однако, разве не было бы фантастически, если бы было что-то, чтобы решить эту проблему? В этой статье я собираюсь провести несколько экспериментов с другим подходом, чтобы обойти это узкое место в производительности.

Решение

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

Код программы тестирования находится в следующем репозитории. Он использует C#, .NET Core, EF Core и PostgreSQL. Я использовал машину с Intel Core i5, 8 ГБ ОЗУ и SSD.

Схема БД для тестирования выглядит так:

Вариант 1. Простой и наивный

Давайте попробуем что-нибудь простое, просто для начала.

 var result = new List<Price>(); using (var context = CreateContext()) { foreach (var testElement in TestData) { result.AddRange(context.Prices.Where( x => x.Security.Ticker == testElement.Ticker && x.TradedOn == testElement.TradedOn && x.PriceSourceId == testElement.PriceSourceId)); } }

Алгоритм прост: для каждого элемента в тестовых данных найдите соответствующий элемент в базе данных и добавьте его в набор результатов. У этого кода есть только одно преимущество: его очень легко реализовать. Кроме того, он удобочитаем и ремонтопригоден. Его очевидный недостаток в том, что он самый медленный. Несмотря на то, что все три столбца проиндексированы, накладные расходы на сетевое взаимодействие по-прежнему создают узкое место в производительности. Вот показатели:

Таким образом, для большого объема требуется примерно одна минута. Потребление памяти кажется разумным.

Вариант 2. Наивный с параллельным

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

 var result = new ConcurrentBag<Price>(); var partitioner = Partitioner.Create(0, TestData.Count); Parallel.ForEach(partitioner, range => { var subList = TestData.Skip(range.Item1) .Take(range.Item2 - range.Item1) .ToList(); using (var context = CreateContext()) { foreach (var testElement in subList) { var query = context.Prices.Where( x => x.Security.Ticker == testElement.Ticker && x.TradedOn == testElement.TradedOn && x.PriceSourceId == testElement.PriceSourceId); foreach (var el in query) { result.Add(el); } } } });

Интересно, что для небольших наборов тестовых данных этот подход работает медленнее, чем первое решение, но для больших выборок быстрее (примерно в 2 раза в данном случае). Потребление памяти немного меняется, но незначительно.

Вариант 3. Множественное содержание

Попробуем другой подход:

• Подготовьте 3 коллекции уникальных значений Ticker, PriceSourceId и Date.
• Выполните запрос с фильтрацией за один проход, используя 3 Содержит.
• Перепроверьте локально (см. ниже).

 var result = new List<Price>(); using (var context = CreateContext()) { var tickers = TestData.Select(x => x.Ticker).Distinct().ToList(); var dates = TestData.Select(x => x.TradedOn).Distinct().ToList(); var ps = TestData.Select(x => x.PriceSourceId) .Distinct().ToList(); var data = context.Prices .Where(x => tickers.Contains(x.Security.Ticker) && dates.Contains(x.TradedOn) && ps.Contains(x.PriceSourceId)) .Select(x => new { x.PriceSourceId, Price = x, Ticker = x.Security.Ticker, }) .ToList(); var lookup = data.ToLookup(x => $"{x.Ticker}, {x.Price.TradedOn}, {x.PriceSourceId}"); foreach (var el in TestData) { var key = $"{el.Ticker}, {el.TradedOn}, {el.PriceSourceId}"; result.AddRange(lookup[key].Select(x => x.Price)); } }

Этот подход проблематичен. Время выполнения очень зависит от данных. Он может получить только необходимые записи (в этом случае он будет очень быстрым), но может вернуть и гораздо больше (может быть, даже в 100 раз больше).

Рассмотрим следующие тестовые данные:

Здесь я запрашиваю цены для Ticker1, торгуемого 01.01.2018, и для Ticker2, торгуемого 02.01.2018. Однако фактически будут возвращены четыре записи.

Уникальными значениями для Ticker являются Ticker1 и Ticker2 . Уникальные значения для TradedOn : 2018-01-01 и 2018-01-02 .

Итак, этому выражению соответствуют четыре записи.

Вот почему нужна локальная перепроверка и чем опасен такой подход. Метрики следующие:

Ужасное потребление памяти! Тесты с большими объемами провалились из-за тайм-аута в 10 минут.

Вариант 4. Построитель предикатов

Давайте изменим парадигму: создадим старое доброе Expression для каждого набора тестовых данных.

 var result = new List<Price>(); using (var context = CreateContext()) { var baseQuery = from p in context.Prices join s in context.Securities on p.SecurityId equals s.SecurityId select new TestData() { Ticker = s.Ticker, TradedOn = p.TradedOn, PriceSourceId = p.PriceSourceId, PriceObject = p }; var tradedOnProperty = typeof(TestData).GetProperty("TradedOn"); var priceSourceIdProperty = typeof(TestData).GetProperty("PriceSourceId"); var tickerProperty = typeof(TestData).GetProperty("Ticker"); var paramExpression = Expression.Parameter(typeof(TestData)); Expression wholeClause = null; foreach (var td in TestData) { var elementClause = Expression.AndAlso( Expression.Equal( Expression.MakeMemberAccess( paramExpression, tradedOnProperty), Expression.Constant(td.TradedOn) ), Expression.AndAlso( Expression.Equal( Expression.MakeMemberAccess( paramExpression, priceSourceIdProperty), Expression.Constant(td.PriceSourceId) ), Expression.Equal( Expression.MakeMemberAccess( paramExpression, tickerProperty), Expression.Constant(td.Ticker)) )); if (wholeClause == null) wholeClause = elementClause; else wholeClause = Expression.OrElse(wholeClause, elementClause); } var query = baseQuery.Where( (Expression<Func<TestData, bool>>)Expression.Lambda( wholeClause, paramExpression)).Select(x => x.PriceObject); result.AddRange(query); }

Полученный код довольно сложен. Построение выражений — не самая простая вещь и требует размышлений (что само по себе не так быстро). Но это помогает нам построить единый запрос, используя множество … (.. AND .. AND ..) OR (.. AND .. AND ..) OR (.. AND .. AND ..) ... . Вот результаты:

Даже хуже, чем любой из предыдущих подходов.

Вариант 5. Общая таблица данных запроса

Давайте попробуем еще один подход:

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

• Начать транзакцию (если она еще не запущена)
• Загрузить данные запроса в эту таблицу (временно)
• Выполнить запрос
• Откатить транзакцию — удалить загруженные данные.

 var result = new List<Price>(); using (var context = CreateContext()) { context.Database.BeginTransaction(); var reducedData = TestData.Select(x => new SharedQueryModel() { PriceSourceId = x.PriceSourceId, Ticker = x.Ticker, TradedOn = x.TradedOn }).ToList(); // Here query data is stored to shared table context.QueryDataShared.AddRange(reducedData); context.SaveChanges(); var query = from p in context.Prices join s in context.Securities on p.SecurityId equals s.SecurityId join t in context.QueryDataShared on new { s.Ticker, p.TradedOn, p.PriceSourceId } equals new { t.Ticker, t.TradedOn, t.PriceSourceId } select p; result.AddRange(query); context.Database.RollbackTransaction(); }

Сначала метрики:

Результат очень хороший. Очень быстро. Потребление памяти тоже хорошее. Но есть недостатки:

• Вы должны создать дополнительную таблицу в базе данных, чтобы выполнить только один тип запроса,
• Вы должны начать транзакцию (которая в любом случае потребляет ресурсы СУБД), и
• Вам нужно что-то записать в базу данных (в операции READ!), и в основном это не сработает, если вы используете что-то вроде реплики чтения.

Но кроме этого, такой подход хорош — быстро и читабельно. И план запроса в этом случае кэшируется!

Вариант 6. Расширение MemoryJoin

Здесь я буду использовать пакет NuGet с именем EntityFrameworkCore.MemoryJoin. Несмотря на то, что в его названии есть слово Core, он также поддерживает EF 6. Он называется MemoryJoin, но на самом деле он отправляет заданные данные запроса в виде VALUES на сервер и вся работа выполняется на SQL-сервере.

Давайте проверим код.

 var result = new List<Price>(); using (var context = CreateContext()) { // better to select needed properties only, for better performance var reducedData = TestData.Select(x => new { x.Ticker, x.TradedOn, x.PriceSourceId }).ToList(); var queryable = context.FromLocalList(reducedData); var query = from p in context.Prices join s in context.Securities on p.SecurityId equals s.SecurityId join t in queryable on new { s.Ticker, p.TradedOn, p.PriceSourceId } equals new { t.Ticker, t.TradedOn, t.PriceSourceId } select p; result.AddRange(query); }

Метрики:

Это выглядит потрясающе. В три раза быстрее, чем предыдущий подход, что делает его самым быстрым. 3,5 секунды для 64K записей! Код простой и понятный. Это работает с репликами только для чтения. Давайте проверим запрос, сгенерированный для трех элементов:

 SELECT "p"."PriceId", "p"."ClosePrice", "p"."OpenPrice", "p"."PriceSourceId", "p"."SecurityId", "p"."TradedOn", "t"."Ticker", "t"."TradedOn", "t"."PriceSourceId" FROM "Price" AS "p" INNER JOIN "Security" AS "s" ON "p"."SecurityId" = "s"."SecurityId" INNER JOIN ( SELECT "x"."string1" AS "Ticker", "x"."date1" AS "TradedOn", CAST("x"."long1" AS int4) AS "PriceSourceId" FROM ( SELECT * FROM ( VALUES (1, @__gen_q_p0, @__gen_q_p1, @__gen_q_p2), (2, @__gen_q_p3, @__gen_q_p4, @__gen_q_p5), (3, @__gen_q_p6, @__gen_q_p7, @__gen_q_p8) ) AS __gen_query_data__ (id, string1, date1, long1) ) AS "x" ) AS "t" ON (("s"."Ticker" = "t"."Ticker") AND ("p"."PriceSourceId" = "t"."PriceSourceId")

Как видите, на этот раз фактические значения передаются из памяти на SQL-сервер в конструкции VALUES. И это помогает: серверу SQL удалось выполнить операцию быстрого соединения и правильно использовать индексы.

Тем не менее, есть некоторые недостатки (подробнее вы можете прочитать в моем блоге):

• Вам нужно добавить дополнительный DbSet в вашу модель (однако нет необходимости создавать его в БД)
• Расширение не поддерживает классы моделей со многими свойствами: три строковых свойства, три свойства даты, три направляющих свойства, три свойства float/double и три свойства int/byte/long/decimal. Думаю, этого более чем достаточно в 90% случаев. Однако, если это не так, вы можете создать собственный класс и использовать его. Итак, ПОДСКАЗКА: вам нужно передавать фактические значения в запросе, иначе ресурсы будут потрачены впустую.

Заключение

Среди вещей, которые я тестировал здесь, я бы определенно выбрал MemoryJoin. Кто-то еще может возразить, что недостатки непреодолимы, а так как не все из них можно устранить на данный момент, то от использования расширения следует воздержаться. Ну, для меня это все равно, что сказать, что нельзя пользоваться ножом, потому что можно порезаться. Оптимизация была задачей не для младших разработчиков, а для тех, кто понимает, как работает EF. С этой целью этот инструмент может значительно повысить производительность. Кто знает? Возможно, однажды кто-нибудь в Microsoft добавит базовую поддержку динамических ЗНАЧЕНИЙ.

Наконец, вот еще несколько диаграмм для сравнения результатов.

Ниже приведена диаграмма времени, необходимого для выполнения операции. MemoryJoin — единственный, который выполняет работу в разумные сроки. Только четыре подхода могут обрабатывать большие объемы: две наивные реализации, общая таблица и MemoryJoin.

Следующая диаграмма предназначена для потребления памяти. Все подходы демонстрируют более или менее одинаковые цифры, кроме варианта с несколькими Contains . Это явление было описано выше.