Enumerable.Any()在大型Linq數據集上非常慢

.net c# entity-framework entity-framework-core linq

我正在運行這樣的代碼:

var somethings = db.Somethings.Select(s => new SomethingViewModel
{
    Id = s.Id,
    Name = s.Name,
    IsActive = s.IsActive
    SubSomethings = s.SubSomethings.Select(ss => new SubSomethingViewModel
    {
        Id = ss.Id,
        Name = ss.Name,
        IsActive = ss.IsActive
    }).Where(wss => wss.IsActive)                        
}).Where(ws => ws.IsActive && (ws.SubSomethings.Any())) //remove elements if no SubSomethings
.ToList();

如您所見,這是一對多的關係。 Something中有一個SubSomethings列表。如果我取出&&(ws.SubSomethings.Any()),我會得到一個非常快速的列表。

但是,我想只在列表中包含至少有一個SubSomething的Somethings。我也嘗試了以下並獲得了同樣可怕的效率:

var somethings = db.Somethings.Select(s => new SomethingViewModel
{
    Id = s.Id,
    Name = s.Name,
    IsActive = s.IsActive
    SubSomethings = s.SubSomethings.Select(ss => new SubSomethingViewModel
    {
        Id = ss.Id,
        Name = ss.Name,
        IsActive = ss.IsActive
    }).Where(wss => wss.IsActive)                        
}).Where(ws => ws.IsActive)
.ToList(); //this finishes very quickly

var somethings2 = somethings.Where(s => s.SubSomethings.Any()).ToList(); //This is where the code bogged down

如何重新編寫查詢以使編碼更快?有一點需要注意:這適用於一兩條記錄。當我達到> 8000條記錄時,至少需要四分鐘。

這是我在SubSomething表上為SomethingId的外鍵創建的索引,它對應於Something.Id

CREATE NONCLUSTERED INDEX [IX_SubSomething_SomethingId] ON [dbo].[SubSomething]
(
    [SomethingId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

這是SubSomething.SomethingId的外鍵創建:

ALTER TABLE [dbo].[SubSomething]  WITH CHECK ADD  CONSTRAINT [FK_SubSomething_Something_SomethingId] FOREIGN KEY([SomethingId])
REFERENCES [dbo].[Something] ([Id])
GO

ALTER TABLE [dbo].[SubSomething] CHECK CONSTRAINT [FK_SubSomething_Something_SomethingId]
GO

一般承認的答案

EF Core是你的問題。目前,當查詢包含子集合投影時,已知執行N + 1子查詢。

解決它並將整個事情限制為2個SQL查詢的唯一方法是盡可能多地過濾,然後使用預先加載在內存中加載包含子實體的整個實體集,然後切換到LINQ to Objects並進行最終投影/過濾:

var somethings = db.Somethings
    .Include(s => s.SubSomethings)
    .Where(s => s.IsActive)
    .AsEnumerable()
    .Select(s => new SomethingViewModel
    {
        Id = s.Id,
        Name = s.Name,
        IsActive = s.IsActive,
        SubSomethings = s.SubSomethings.Select(ss => new SubSomethingViewModel
        {
            Id = ss.Id,
            Name = ss.Name,
            IsActive = ss.IsActive
        }).Where(wss => wss.IsActive).ToList()
    })
    .Where(s => s.SubSomethings.Any())
    .ToList();

熱門答案

正如Ivan Stoev所說,EF Core是你的問題,我分享了一種不使用LINQ to SQL的替代方案,但是這種方法有更好的表現:

  1. 使用邏輯創建數據庫中的視圖以加入所需的數據
  2. 使用db.Database.SqlQuery(“Query”)查詢視圖並返回數據:

    var results = db.Database.SqlQuery <SomethingViewModel>(“從ViewSomething中選擇ColA,ColB,ColC”)

    SomethingViewModel對象必須是一個強類型,其propreties匹配視圖的cols。

我知道這不是很漂亮,並且對視圖有硬編碼查詢,但通常是您通過編譯器後使用EF獲得的最快性能。



Related

許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow