Optimize Search and NextUp queries

4 files changed, 87 insertions, 67 deletions

diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
index 0a8f8627b4..907d8527aa 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
@@ -99,10 +99,9 @@ public sealed partial class BaseItemRepository
             query = query.Where(e => !excludeItemTypes.Contains(e.Item.Type));
         }
 
-        // query = query.DistinctBy(e => e.CleanValue);
         return query.Select(e => e.ItemValue)
             .GroupBy(e => e.CleanValue)
-            .Select(e => e.OrderBy(v => v.Value).First().Value)
+            .Select(g => g.Min(v => v.Value)!)
             .ToArray();
     }
 
@@ -133,17 +132,22 @@ public sealed partial class BaseItemRepository
             IsNews = filter.IsNews,
             IsSeries = filter.IsSeries
         });
-        var itemValuesQuery = context.ItemValuesMap
+
+        // Materialize the matching CleanValues early. This splits one massive expression tree
+        // into two simpler queries, dramatically reducing EF Core expression compilation time.
+        var matchingCleanValues = context.ItemValuesMap
             .Where(ivm => itemValueTypes.Contains(ivm.ItemValue.Type))
             .Join(
                 innerQueryFilter,
                 ivm => ivm.ItemId,
                 g => g.Id,
-                (ivm, g) => ivm.ItemValue.CleanValue);
+                (ivm, g) => ivm.ItemValue.CleanValue)
+            .Distinct()
+            .ToList();
 
         var innerQuery = PrepareItemQuery(context, filter)
             .Where(e => e.Type == returnType)
-            .Where(e => itemValuesQuery.Contains(e.CleanName));
+            .Where(e => matchingCleanValues.Contains(e.CleanName!));
 
         var outerQueryFilter = new InternalItemsQuery(filter.User)
         {
@@ -166,43 +170,40 @@ public sealed partial class BaseItemRepository
             ExcludeItemIds = filter.ExcludeItemIds
         };
 
-        var masterQuery = TranslateQuery(innerQuery, context, outerQueryFilter)
-            .GroupBy(e => e.PresentationUniqueKey)
-            .Select(e => e.OrderBy(x => x.Id).FirstOrDefault())
-            .Select(e => e!.Id);
-
-        var query = context.BaseItems
-            .Include(e => e.TrailerTypes)
-            .Include(e => e.Provider)
-            .Include(e => e.LockedFields)
-            .Include(e => e.Images)
-            .Include(e => e.LinkedChildEntities)
-            .AsSingleQuery()
-            .Where(e => masterQuery.Contains(e.Id));
+        // Materialize the matching IDs first. This keeps the complex nested subquery
+        // (inner filter + ItemValues join + search + GroupBy) as a single simple SQL statement,
+        // and then the entity load with Includes uses a flat WHERE Id IN (...) list.
+        // This avoids EF having to compile the entire nested expression tree into the final query.
+        var masterQuery = TranslateQuery(innerQuery, context, outerQueryFilter);
 
-        query = ApplyOrder(query, filter, context);
+        var orderedMasterQuery = ApplyOrder(masterQuery, filter, context)
+            .GroupBy(e => e.PresentationUniqueKey)
+            .Select(g => g.Min(e => e.Id));
 
         var result = new QueryResult<(BaseItemDto, ItemCounts?)>();
         if (filter.EnableTotalRecordCount)
         {
-            result.TotalRecordCount = query.Count();
+            result.TotalRecordCount = orderedMasterQuery.Count();
         }
 
-        if (filter.Limit.HasValue || filter.StartIndex.HasValue)
+        if (filter.StartIndex.HasValue && filter.StartIndex.Value > 0)
         {
-            var offset = filter.StartIndex ?? 0;
-
-            if (offset > 0)
-            {
-                query = query.Skip(offset);
-            }
+            orderedMasterQuery = orderedMasterQuery.Skip(filter.StartIndex.Value);
+        }
 
-            if (filter.Limit.HasValue)
-            {
-                query = query.Take(filter.Limit.Value);
-            }
+        if (filter.Limit.HasValue)
+        {
+            orderedMasterQuery = orderedMasterQuery.Take(filter.Limit.Value);
         }
 
+        var masterIds = orderedMasterQuery.ToList();
+
+        var query = ApplyNavigations(
+                context.BaseItems.AsSingleQuery().Where(e => masterIds.Contains(e.Id)),
+                filter);
+
+        query = ApplyOrder(query, filter, context);
+
         if (filter.IncludeItemTypes.Length > 0)
         {
             var typeSubQuery = new InternalItemsQuery(filter.User)
@@ -229,8 +230,8 @@ public sealed partial class BaseItemRepository
             var audioTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Audio];
             var trailerTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Trailer];
 
-            // Get the IDs from itemCountQuery to use in the join
-            var itemIds = itemCountQuery.Select(e => e.Id);
+            // Materialize the matching IDs to avoid nested subquery in the counts expression tree.
+            var itemIds = itemCountQuery.Select(e => e.Id).ToList();
 
             // Rewrite query to avoid SelectMany on navigation properties (which requires SQL APPLY, not supported on SQLite)
             // Instead, start from ItemValueMaps and join with BaseItems
diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs
index b1f7326d06..83f108bed0 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs
@@ -68,22 +68,24 @@ public sealed partial class BaseItemRepository
         // for that case the invoker has to run a DistinctBy(e => e.PresentationUniqueKey) on their own
 
         var enableGroupByPresentationUniqueKey = EnableGroupByPresentationUniqueKey(filter);
-        // Use Min(Id) instead of OrderBy(Id).FirstOrDefault() to avoid EF Core generating
-        // a correlated scalar subquery per group.
+        // Materialize GroupBy IDs first to split the complex expression tree.
+        // This runs the filter+GroupBy+Min as one simple SQL query, then the downstream
+        // Order/Paging/Navigations work on a flat WHERE Id IN (...) list, avoiding
+        // EF Core having to compile a deeply nested expression tree.
         if (enableGroupByPresentationUniqueKey && filter.GroupBySeriesPresentationUniqueKey)
         {
-            var tempQuery = dbQuery.GroupBy(e => new { e.PresentationUniqueKey, e.SeriesPresentationUniqueKey }).Select(e => e.Min(x => x.Id));
-            dbQuery = context.BaseItems.Where(e => tempQuery.Contains(e.Id));
+            var groupedIds = dbQuery.GroupBy(e => new { e.PresentationUniqueKey, e.SeriesPresentationUniqueKey }).Select(e => e.Min(x => x.Id)).ToList();
+            dbQuery = context.BaseItems.Where(e => groupedIds.Contains(e.Id));
         }
         else if (enableGroupByPresentationUniqueKey)
         {
-            var tempQuery = dbQuery.GroupBy(e => e.PresentationUniqueKey).Select(e => e.Min(x => x.Id));
-            dbQuery = context.BaseItems.Where(e => tempQuery.Contains(e.Id));
+            var groupedIds = dbQuery.GroupBy(e => e.PresentationUniqueKey).Select(e => e.Min(x => x.Id)).ToList();
+            dbQuery = context.BaseItems.Where(e => groupedIds.Contains(e.Id));
         }
         else if (filter.GroupBySeriesPresentationUniqueKey)
         {
-            var tempQuery = dbQuery.GroupBy(e => e.SeriesPresentationUniqueKey).Select(e => e.Min(x => x.Id));
-            dbQuery = context.BaseItems.Where(e => tempQuery.Contains(e.Id));
+            var groupedIds = dbQuery.GroupBy(e => e.SeriesPresentationUniqueKey).Select(e => e.Min(x => x.Id)).ToList();
+            dbQuery = context.BaseItems.Where(e => groupedIds.Contains(e.Id));
         }
         else
         {
diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs
index f1899cd2de..7ca3559324 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs
@@ -181,7 +181,7 @@ public sealed partial class BaseItemRepository
         var firstIds = allItemsLite
             .DistinctBy(e => e.GroupKey)
             .Select(e => e.Id)
-            .AsEnumerable();
+            .ToList();
 
         var itemsQuery = context.BaseItems.AsNoTracking().Where(e => firstIds.Contains(e.Id));
         itemsQuery = ApplyNavigations(itemsQuery, filter);
@@ -236,13 +236,18 @@ public sealed partial class BaseItemRepository
             topSeriesWithDates = topSeriesWithDates.Take(limit.Value).OrderByDescending(g => g.MaxDate);
         }
 
-        var topSeriesNames = topSeriesWithDates.Select(g => g.SeriesName).AsEnumerable();
+        // Materialize series names and cutoff to avoid embedding the GroupBy+OrderBy
+        // expression tree as a subquery inside the episode query.
+        var topSeriesData = topSeriesWithDates
+            .Select(g => new { g.SeriesName, g.MaxDate })
+            .ToList();
+        var topSeriesNames = topSeriesData.Select(g => g.SeriesName).ToList();
 
         // Compute a global date cutoff: the oldest series' max date minus the window.
         // Episodes before this cutoff cannot be in any series' "recent additions" window,
         // so we can safely exclude them to avoid loading ancient episodes.
-        var globalCutoff = topSeriesWithDates.Any()
-            ? topSeriesWithDates.Min(g => g.MaxDate)?.AddHours(-RecentAdditionWindowHours)
+        var globalCutoff = topSeriesData.Count > 0
+            ? topSeriesData.Min(g => g.MaxDate)?.AddHours(-RecentAdditionWindowHours)
             : null;
 
         // Fetch only the columns needed for analysis (lightweight projection).
@@ -530,7 +535,7 @@ public sealed partial class BaseItemRepository
             .Where(ivm => matchingItemIds.Contains(ivm.ItemId))
             .Select(ivm => ivm.ItemValue)
             .GroupBy(iv => iv.CleanValue)
-            .Select(g => g.OrderBy(iv => iv.Value).First().Value)
+            .Select(g => g.Min(iv => iv.Value))
             .OrderBy(t => t)
             .ToArray();
 
@@ -539,7 +544,7 @@ public sealed partial class BaseItemRepository
             .Where(ivm => matchingItemIds.Contains(ivm.ItemId))
             .Select(ivm => ivm.ItemValue)
             .GroupBy(iv => iv.CleanValue)
-            .Select(g => g.OrderBy(iv => iv.Value).First().Value)
+            .Select(g => g.Min(iv => iv.Value))
             .OrderBy(g => g)
             .ToArray();
 
diff --git a/Jellyfin.Server.Implementations/Item/NextUpService.cs b/Jellyfin.Server.Implementations/Item/NextUpService.cs
index 1f5616bb5e..b25b347868 100644
--- a/Jellyfin.Server.Implementations/Item/NextUpService.cs
+++ b/Jellyfin.Server.Implementations/Item/NextUpService.cs
@@ -97,17 +97,28 @@ public class NextUpService : INextUpService
             .Where(e => e.ParentIndexNumber != 0)
             .Where(e => e.UserData!.Any(ud => ud.UserId == userId && ud.Played));
         lastWatchedBase = _queryHelpers.ApplyAccessFiltering(context, lastWatchedBase, filter);
-        var lastWatchedInfo = lastWatchedBase
-            .GroupBy(e => e.SeriesPresentationUniqueKey)
-            .Select(g => new
+
+        // Use lightweight projection + client-side grouping to avoid correlated scalar subquery
+        // per group that EF generates for GroupBy+OrderByDescending+FirstOrDefault.
+        var allPlayedLite = lastWatchedBase
+            .Select(e => new
             {
-                SeriesKey = g.Key!,
-                LastWatchedId = g.OrderByDescending(e => e.ParentIndexNumber)
-                                 .ThenByDescending(e => e.IndexNumber)
-                                 .Select(e => e.Id)
-                                 .FirstOrDefault()
+                e.Id,
+                e.SeriesPresentationUniqueKey,
+                e.ParentIndexNumber,
+                e.IndexNumber
             })
-            .ToDictionary(x => x.SeriesKey, x => x.LastWatchedId);
+            .ToList();
+
+        var lastWatchedInfo = new Dictionary<string, Guid>();
+        foreach (var group in allPlayedLite.GroupBy(e => e.SeriesPresentationUniqueKey))
+        {
+            var lastWatched = group
+                .OrderByDescending(e => e.ParentIndexNumber)
+                .ThenByDescending(e => e.IndexNumber)
+                .First();
+            lastWatchedInfo[group.Key!] = lastWatched.Id;
+        }
 
         Dictionary<string, Guid> lastWatchedByDateInfo = new();
         if (includeWatchedForRewatching)
@@ -119,18 +130,19 @@ public class NextUpService : INextUpService
                 .Where(e => e.ParentIndexNumber != 0)
                 .Where(e => e.UserData!.Any(ud => ud.UserId == userId && ud.Played));
             lastWatchedByDateBase = _queryHelpers.ApplyAccessFiltering(context, lastWatchedByDateBase, filter);
-            lastWatchedByDateInfo = lastWatchedByDateBase
+
+            // Use lightweight projection + client-side grouping instead of
+            // SelectMany+GroupBy+OrderByDescending+FirstOrDefault (correlated subquery).
+            var playedWithDates = lastWatchedByDateBase
                 .SelectMany(e => e.UserData!.Where(ud => ud.UserId == userId && ud.Played)
-                    .Select(ud => new { Episode = e, ud.LastPlayedDate }))
-                .GroupBy(x => x.Episode.SeriesPresentationUniqueKey)
-                .Select(g => new
-                {
-                    SeriesKey = g.Key!,
-                    LastWatchedId = g.OrderByDescending(x => x.LastPlayedDate)
-                                     .Select(x => x.Episode.Id)
-                                     .FirstOrDefault()
-                })
-                .ToDictionary(x => x.SeriesKey, x => x.LastWatchedId);
+                    .Select(ud => new { EpisodeId = e.Id, e.SeriesPresentationUniqueKey, ud.LastPlayedDate }))
+                .ToList();
+
+            foreach (var group in playedWithDates.GroupBy(x => x.SeriesPresentationUniqueKey))
+            {
+                var mostRecent = group.OrderByDescending(x => x.LastPlayedDate).First();
+                lastWatchedByDateInfo[group.Key!] = mostRecent.EpisodeId;
+            }
         }
 
         var allLastWatchedIds = lastWatchedInfo.Values