Reduce correlated subqueries to improve performance

3 files changed, 145 insertions, 57 deletions

diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
index 6fe8563bf5..1d34346888 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
@@ -4,6 +4,7 @@ using System;
 using System.Collections.Generic;
 using System.Linq;
 using Jellyfin.Data.Enums;
+using Jellyfin.Database.Implementations;
 using Jellyfin.Database.Implementations.Entities;
 using MediaBrowser.Controller.Entities;
 using MediaBrowser.Model.Dto;
@@ -109,7 +110,6 @@ public sealed partial class BaseItemRepository
                 IsNews = filter.IsNews,
                 IsSeries = filter.IsSeries
             })
-            .Where(e => e.MediaStreams != null)
             .SelectMany(e => e.MediaStreams!)
             .Where(e => e.StreamType == (MediaStreamTypeEntity)mediaStreamType)
             .Select(s => string.IsNullOrEmpty(s.Language) ? "und" : s.Language) // und = undetermined
@@ -168,9 +168,7 @@ public sealed partial class BaseItemRepository
             IsSeries = filter.IsSeries
         });
 
-        // Keep this as an IQueryable sub-select. Materializing to a list would inline one
-        // bound parameter per CleanValue and hit SQLite's variable cap on libraries with
-        // high-cardinality value types (e.g. tens of thousands of artists).
+        // Resolve, then materialize, the set of clean values belonging to items that match the inner filter.
         var matchingCleanValues = context.ItemValuesMap
             .Where(ivm => itemValueTypes.Contains(ivm.ItemValue.Type))
             .Join(
@@ -178,11 +176,13 @@ public sealed partial class BaseItemRepository
                 ivm => ivm.ItemId,
                 g => g.Id,
                 (ivm, g) => ivm.ItemValue.CleanValue)
-            .Distinct();
+            .Distinct()
+            .ToList();
 
+        // Match CleanName against the resolved clean values.
         var innerQuery = PrepareItemQuery(context, filter)
             .Where(e => e.Type == returnType)
-            .Where(e => matchingCleanValues.Contains(e.CleanName!));
+            .WhereOneOrMany(matchingCleanValues, e => e.CleanName!);
 
         var outerQueryFilter = new InternalItemsQuery(filter.User)
         {
@@ -205,32 +205,42 @@ public sealed partial class BaseItemRepository
             ExcludeItemIds = filter.ExcludeItemIds
         };
 
-        // Collapse rows that share a PresentationUniqueKey (e.g. alternate versions) by picking
-        // the lowest Id per group. For MusicArtist, prefer the entity from a library the user
-        // can actually access,since the same artist can have a folder in multiple libraries.
-        // Keep as an IQueryable sub-select so paging is applied AFTER
-        // ApplyOrder runs the caller's actual sort.
+        // Collapse rows that share a PresentationUniqueKey (e.g. alternate versions) into one
+        // representative id per group, then materialize the representative ids once.
         var masterQuery = TranslateQuery(innerQuery, context, outerQueryFilter);
         var isMusicArtist = returnType == _itemTypeLookup.BaseItemKindNames[BaseItemKind.MusicArtist];
-        var representativeIds = isMusicArtist
-            ? masterQuery
+        List<Guid> representativeIds;
+        if (isMusicArtist)
+        {
+            // For MusicArtist, prefer the entity from a library the user can actually access.
+            // Materilaize to prevent correlated per-group first-row queries which hurt performance.
+            var topParentIds = filter.TopParentIds;
+            representativeIds = masterQuery
+                .Select(e => new { e.Id, e.PresentationUniqueKey, e.TopParentId })
+                .AsEnumerable()
                 .GroupBy(e => e.PresentationUniqueKey)
                 .Select(g => g
-                    .OrderBy(e => filter.TopParentIds.Contains(e.TopParentId ?? Guid.Empty) ? 0 : 1)
+                    .OrderBy(e => topParentIds.Contains(e.TopParentId ?? Guid.Empty) ? 0 : 1)
                     .ThenBy(e => e.Id)
                     .First().Id)
-            : masterQuery
+                .ToList();
+        }
+        else
+        {
+            representativeIds = masterQuery
                 .GroupBy(e => e.PresentationUniqueKey)
-                .Select(g => g.Min(e => e.Id));
+                .Select(g => g.Min(e => e.Id))
+                .ToList();
+        }
 
         var result = new QueryResult<(BaseItemDto, ItemCounts?)>();
         if (filter.EnableTotalRecordCount)
         {
-            result.TotalRecordCount = representativeIds.Count();
+            result.TotalRecordCount = representativeIds.Count;
         }
 
         var query = ApplyNavigations(
-                context.BaseItems.AsNoTracking().AsSingleQuery().Where(e => representativeIds.Contains(e.Id)),
+                context.BaseItems.AsNoTracking().AsSingleQuery().WhereOneOrMany(representativeIds, e => e.Id),
                 filter);
 
         query = ApplyOrder(query, filter, context);
@@ -311,8 +321,8 @@ public sealed partial class BaseItemRepository
         var itemIds = itemCountQuery.Select(e => e.Id);
 
         // Rewrite query to avoid SelectMany on navigation properties (which requires SQL APPLY, not supported on SQLite)
-        // Instead, start from ItemValueMaps and join with BaseItems
-        return context.ItemValuesMap
+        // Instead, start from ItemValueMaps and join with BaseItems.
+        var rawCounts = context.ItemValuesMap
             .Where(ivm => itemValueTypes.Contains(ivm.ItemValue.Type))
             .Where(ivm => itemIds.Contains(ivm.ItemId))
             .Join(
@@ -322,18 +332,47 @@ public sealed partial class BaseItemRepository
                 (ivm, e) => new { CleanName = ivm.ItemValue.CleanValue, e.Type })
             .GroupBy(x => new { x.CleanName, x.Type })
             .Select(g => new { g.Key.CleanName, g.Key.Type, Count = g.Count() })
-            .GroupBy(x => x.CleanName)
-            .ToDictionary(
-                g => g.Key,
-                g => new ItemCounts
+            .AsEnumerable();
+
+        var countsByCleanName = new Dictionary<string, ItemCounts>();
+        foreach (var group in rawCounts.GroupBy(x => x.CleanName))
+        {
+            var counts = new ItemCounts();
+            foreach (var row in group)
+            {
+                if (row.Type == seriesTypeName)
                 {
-                    SeriesCount = g.Where(x => x.Type == seriesTypeName).Sum(x => x.Count),
-                    EpisodeCount = g.Where(x => x.Type == episodeTypeName).Sum(x => x.Count),
-                    MovieCount = g.Where(x => x.Type == movieTypeName).Sum(x => x.Count),
-                    AlbumCount = g.Where(x => x.Type == musicAlbumTypeName).Sum(x => x.Count),
-                    ArtistCount = g.Where(x => x.Type == musicArtistTypeName).Sum(x => x.Count),
-                    SongCount = g.Where(x => x.Type == audioTypeName).Sum(x => x.Count),
-                    TrailerCount = g.Where(x => x.Type == trailerTypeName).Sum(x => x.Count),
-                });
+                    counts.SeriesCount += row.Count;
+                }
+                else if (row.Type == episodeTypeName)
+                {
+                    counts.EpisodeCount += row.Count;
+                }
+                else if (row.Type == movieTypeName)
+                {
+                    counts.MovieCount += row.Count;
+                }
+                else if (row.Type == musicAlbumTypeName)
+                {
+                    counts.AlbumCount += row.Count;
+                }
+                else if (row.Type == musicArtistTypeName)
+                {
+                    counts.ArtistCount += row.Count;
+                }
+                else if (row.Type == audioTypeName)
+                {
+                    counts.SongCount += row.Count;
+                }
+                else if (row.Type == trailerTypeName)
+                {
+                    counts.TrailerCount += row.Count;
+                }
+            }
+
+            countsByCleanName[group.Key] = counts;
+        }
+
+        return countsByCleanName;
     }
 }
diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs
index dc16c3b1b3..00af0bcbeb 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs
@@ -126,38 +126,60 @@ public sealed partial class BaseItemRepository
 
         if (collectionType is CollectionType.movies)
         {
-            // Group by PresentationUniqueKey, pick the newest item per group.
-            var topGroupItems = baseQuery
+            // Pick, per PresentationUniqueKey, the newest item; return the newest `limit` of those.
+            // Build up until limit by streaming through results and deduplicating on the fly.
+            var orderedIds = baseQuery
                 .Where(e => e.PresentationUniqueKey != null)
-                .GroupBy(e => e.PresentationUniqueKey)
-                .Select(g => new
-                {
-                    MaxDate = g.Max(e => e.DateCreated),
-                    FirstId = g.OrderByDescending(e => e.DateCreated).ThenByDescending(e => e.Id).Select(e => e.Id).First()
-                })
-                .OrderByDescending(g => g.MaxDate);
+                .OrderByDescending(e => e.DateCreated)
+                .ThenByDescending(e => e.Id)
+                .Select(e => new { e.Id, e.PresentationUniqueKey });
 
-            var firstIdsQuery = filter.Limit.HasValue
-                ? topGroupItems.Take(filter.Limit.Value).Select(g => g.FirstId)
-                : topGroupItems.Select(g => g.FirstId);
+            var seenKeys = new HashSet<string>();
+            var firstIds = new List<Guid>(limit ?? 0);
+            foreach (var row in orderedIds.AsEnumerable())
+            {
+                if (seenKeys.Add(row.PresentationUniqueKey!))
+                {
+                    firstIds.Add(row.Id);
+                    if (limit.HasValue && firstIds.Count >= limit.Value)
+                    {
+                        break;
+                    }
+                }
+            }
 
-            return LoadLatestByIds(context, firstIdsQuery, filter);
+            return LoadLatestByIds(context, firstIds, filter);
         }
 
-        // Albums whose Id is the parent of any track matching the user's filter.
-        var albumIdsWithMatchingTrack = context.AncestorIds
-            .Join(baseQuery, ai => ai.ItemId, t => t.Id, (ai, _) => ai.ParentItemId);
-
         var musicAlbumTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.MusicAlbum]!;
-        var topAlbumsQuery = context.BaseItems.AsNoTracking()
-            .Where(album => album.Type == musicAlbumTypeName)
-            .Where(album => albumIdsWithMatchingTrack.Contains(album.Id))
+        IQueryable<BaseItemEntity> topAlbumsQuery;
+
+        // When the query is scoped to whole libraries, read the newest albums directly by their own TopParentId.
+        if (filter.TopParentIds.Length > 0)
+        {
+            topAlbumsQuery = context.BaseItems.AsNoTracking()
+                .Where(album => album.Type == musicAlbumTypeName
+                    && !album.IsVirtualItem
+                    && album.TopParentId.HasValue)
+                .WhereOneOrMany(filter.TopParentIds, album => album.TopParentId!.Value);
+        }
+        else
+        {
+            // Fallback (e.g. AncestorIds-scoped callers): albums that are the parent of a matching track.
+            var albumIdsWithMatchingTrack = context.AncestorIds
+                .Join(baseQuery, ai => ai.ItemId, t => t.Id, (ai, _) => ai.ParentItemId);
+            topAlbumsQuery = context.BaseItems.AsNoTracking()
+                .Where(album => album.Type == musicAlbumTypeName)
+                .Where(album => albumIdsWithMatchingTrack.Contains(album.Id));
+        }
+
+        var orderedAlbums = topAlbumsQuery
             .OrderByDescending(album => album.DateCreated)
             .ThenByDescending(album => album.Id);
 
-        var albumIdsQuery = filter.Limit.HasValue
-            ? topAlbumsQuery.Take(filter.Limit.Value).Select(a => a.Id)
-            : topAlbumsQuery.Select(a => a.Id);
+        var albumIdsQuery = limit.HasValue
+            ? orderedAlbums.Take(limit.Value).Select(a => a.Id)
+            : orderedAlbums.Select(a => a.Id);
 
         return LoadLatestByIds(context, albumIdsQuery, filter);
     }
@@ -181,6 +203,29 @@ public sealed partial class BaseItemRepository
             .ToArray()!;
     }
 
+    private IReadOnlyList<BaseItemDto> LoadLatestByIds(
+        JellyfinDbContext context,
+        List<Guid> ids,
+        InternalItemsQuery filter)
+    {
+        if (ids.Count == 0)
+        {
+            return [];
+        }
+
+        var itemsQuery = ApplyNavigations(
+            context.BaseItems.AsNoTracking().WhereOneOrMany(ids, e => e.Id),
+            filter);
+
+        return itemsQuery
+            .OrderByDescending(e => e.DateCreated)
+            .ThenByDescending(e => e.Id)
+            .AsEnumerable()
+            .Select(w => DeserializeBaseItem(w, filter.SkipDeserialization))
+            .Where(dto => dto != null)
+            .ToArray()!;
+    }
+
     /// <summary>
     /// Gets the latest TV show items with smart Season/Series container selection.
     /// </summary>
diff --git a/Jellyfin.Server.Implementations/Item/PeopleRepository.cs b/Jellyfin.Server.Implementations/Item/PeopleRepository.cs
index eb87b525fe..9611c5c13a 100644
--- a/Jellyfin.Server.Implementations/Item/PeopleRepository.cs
+++ b/Jellyfin.Server.Implementations/Item/PeopleRepository.cs
@@ -79,7 +79,11 @@ public class PeopleRepository(IDbContextFactory<JellyfinDbContext> dbProvider, I
     public IReadOnlyList<string> GetPeopleNames(InternalPeopleQuery filter)
     {
         using var context = _dbProvider.CreateDbContext();
-        var dbQuery = TranslateQuery(context.Peoples.AsNoTracking(), context, filter).Select(e => e.Name).Distinct();
+
+        IQueryable<string> dbQuery = TranslateQuery(context.Peoples.AsNoTracking(), context, filter)
+            .Select(e => e.Name)
+            .Distinct()
+            .OrderBy(e => e);
 
         if (filter.StartIndex.HasValue && filter.StartIndex > 0)
         {
@@ -88,7 +92,7 @@ public class PeopleRepository(IDbContextFactory<JellyfinDbContext> dbProvider, I
 
         if (filter.Limit > 0)
         {
-            dbQuery = dbQuery.OrderBy(e => e).Take(filter.Limit);
+            dbQuery = dbQuery.Take(filter.Limit);
         }
 
         return dbQuery.ToArray();