Проблемы производительности с ядром EF QueryFilter
В нашей системе мы столкнулись с проблемами производительности при использовании QueryFilters в ядре EF. Проблема в том, что ядро EF фильтрует внутри LEFT JOIN, а не вне его.
Сгенерированный SQL выглядит примерно так:
SELECT [pom].[Id],
[pom].[DeleteDate],
[pom].[UpdateDate],
[pom].[Version],
[t].[Id],
[t].[MandatorId],
[t].[NetPriceAmount],
[t].[NetPriceCurrencyIso4217Code]
FROM [externaldata].[PurchaseOfferMetadata] AS [pom]
LEFT JOIN (
SELECT [po].[Id],
[po].[MandatorId],
[po].[NetPriceAmount],
[po].[NetPriceCurrencyIso4217Code]
FROM [externaldata].[PurchaseOffer] AS [po]
WHERE [po].[MandatorId] = 1
) AS [t] ON [pom].[Id] = [t].[Id]
WHERE [pom].[Id] IN
(CAST(3094411 AS bigint),
CAST(4757070 AS bigint),
CAST(4757112 AS bigint),
CAST(5571232 AS bigint))
Проблемная часть WHERE [po].[MandatorId] = 1.
Если бы это было во втором операторе WHERE, запрос выполнялся бы намного быстрее.
Модель базы данных настроена следующим образом:
modelBuilder.Entity<PurchaseOffer>()
.HasQueryFilter(po => po.MandatorId == 1)
.ToTable(nameof(PurchaseOffer), schema: ExternalDataSchemaName);
modelBuilder.Entity<PurchaseOfferMetadata>()
.HasOne(pom => pom.PurchaseOffer)
.WithOne(po => po.Metadata)
.HasForeignKey<PurchaseOffer>(po => po.Id);
В базе данных мы установили внешний ключ следующим образом:
IF OBJECT_ID('[externaldata].[FK_PurchaseOffer_PurchaseOfferMetadata]', 'F') IS NULL
BEGIN
ALTER TABLE [externaldata].[PurchaseOffer] ADD CONSTRAINT [FK_PurchaseOffer_PurchaseOfferMetadata] FOREIGN KEY
(
[Id]
)
REFERENCES [externaldata].[PurchaseOfferMetadata] ([Id])
END;
Основной запрос EF выглядит следующим образом:
var existingPurchaseOfferMetadatasById = await db.PurchaseOfferMetadatas
.Where(pom => purchaseOfferIds.Contains(pom.Id))
.Include(pom => pom.PurchaseOffer)
.ToDictionaryAsync(pom => pom.Id, cancellationToken);
В настоящее время у нас есть следующее количество записей в каждой таблице:
- ПокупкаПредложениеМетаданные: 12 654 639
- ПокупкаПредложение: 1'689'634
Кто-нибудь также сталкивался с этой проблемой и может найти решение?
EF выдал именно тот запрос, который вы написали в инструкции LINQ. .Include() также должен включать тех родителей, у которых нет детей, поэтому он всегда будет выполнять левое внешнее соединение (а правая таблица имеет фильтр в качестве «базового» фильтра). Звучит так, как будто вы хотите сделать вместо этого внутреннее соединение, которое, как вы заметите, также отфильтровывает родителей без дочерних элементов, и в этом случае вам будет полезно указать его, то есть с помощью функции .Join (ВНУТРЕННЕЕ соединение), или указать дополнительный фильтр на родительский набор данных.
Вы использовали фильтр запроса в таблице заказов .HasQueryFilter(po => po.MandatorId == 1), который будет применяться везде, где есть ссылка на таблицу PurchaseOrder, как на нее ссылаются. Если вы удалите этот QueryFilter и вместо этого добавите предложение where в свой запрос, это должно лучше оптимизировать запрос. Если вы используете шаблон репозитория или тому подобное для управления поиском данных, я бы подумал о размещении этих условий низкого уровня, а не построителя модели. Построитель контекстной модели является скорее гарантией, но я подозреваю, что это приведет к проблемам с производительностью ради соответствия.
@StevePy Мы сделали именно то, что вы сказали, и это значительно улучшило производительность. Тем не менее, запросы не идеальны, и мы ищем другие идеи для повышения производительности. В EF core 3.0 могут быть внесены некоторые оптимизации: EF Core 3.0
Первое и главное улучшение производительности, которое вы можете сделать с помощью EF, — это использовать .Select() для заполнения более простых моделей представления из вашего графа сущностей, а не извлекать сущности и связанные с ними биты. (либо с ленивой загрузкой Выберите N+1 ловушки, либо даже с жадной загрузкой) Выбор меньшего количества данных означает меньший объем памяти на сервере, меньше данных по сети и позволяет лучше настроить индексирование для обычных операций. Для слишком сложных моделей, таких как сценарии, похожие на отчеты, вы можете привязать сущности к материализованным представлениям, чтобы сохранить простоту кода и сложность базы данных.
Ответы 1
Исторически сложилось так, что EF не очень хорошо справляется с созданием наиболее оптимизированных SQL-запросов, когда соединения являются сложными. Я не могу ответить, как заставить Linq понять дизайн оптимизации вашей базы данных; однако, если вы знаете SQL, который должен быть сгенерирован, я предлагаю для повышения производительности сложного чтения использовать пакет NuGet, такой как щеголеватый, чтобы написать именно тот SQL, который вы хотите, и получить соединения, которых заслуживает ваша база данных.
using Dapper;
[...]
public class SalsaZima
{
public int ID { get; set; }
public string MySalsaColumn { get; set; }
public string MyZimaColumn { get; set; }
}
[...]
// get data from database
using (IDbConnection dp = Dapper)
{
string query = @"SELECT
s.ID
,s.MySalsaColumn
,z.MyZimaColumn
FROM dbo.Salsa s
LEFT JOIN dbo.Zima z
ON s.ZimaID = z.ID";
return dp.Query<SalsaZima>(query).ToList();
}
Связано: stackoverflow.com/questions/43569749/…
Если вы выберете Dapper, вы все равно сможете использовать EF и Linq в более простых сценариях. Я использую Dapper только там, где соединения усложняются, а производительность чтения проблематична.
Возможный дубликат Как избежать повторной компиляции плана запроса при использовании IEnumerable.Contains в запросах Entity Framework LINQ?