Speed-up LatestItems for Music

2 files changed, 96 insertions, 43 deletions

diff --git a/Emby.Server.Implementations/Dto/DtoService.cs b/Emby.Server.Implementations/Dto/DtoService.cs
index cc57d183b6..9f36577410 100644
--- a/Emby.Server.Implementations/Dto/DtoService.cs
+++ b/Emby.Server.Implementations/Dto/DtoService.cs
@@ -203,6 +203,39 @@ namespace Emby.Server.Implementations.Dto
                 }
             }
 
+            // Batch-fetch MusicArtist lookups across all items to avoid N+1 queries.
+            IReadOnlyDictionary<string, MusicArtist[]>? artistsBatch = null;
+            HashSet<string>? artistNames = null;
+            foreach (var item in accessibleItems)
+            {
+                if (item is IHasArtist hasArtist)
+                {
+                    foreach (var name in hasArtist.Artists)
+                    {
+                        if (!string.IsNullOrWhiteSpace(name))
+                        {
+                            (artistNames ??= new HashSet<string>(StringComparer.Ordinal)).Add(name);
+                        }
+                    }
+                }
+
+                if (item is IHasAlbumArtist hasAlbumArtist)
+                {
+                    foreach (var name in hasAlbumArtist.AlbumArtists)
+                    {
+                        if (!string.IsNullOrWhiteSpace(name))
+                        {
+                            (artistNames ??= new HashSet<string>(StringComparer.Ordinal)).Add(name);
+                        }
+                    }
+                }
+            }
+
+            if (artistNames is { Count: > 0 })
+            {
+                artistsBatch = _libraryManager.GetArtists(artistNames.ToArray());
+            }
+
             for (int index = 0; index < accessibleItems.Count; index++)
             {
                 var item = accessibleItems[index];
@@ -214,7 +247,8 @@ namespace Emby.Server.Implementations.Dto
                     userDataBatch?.GetValueOrDefault(item.Id),
                     allCollectionFolders,
                     childCountBatch,
-                    playedCountBatch);
+                    playedCountBatch,
+                    artistsBatch);
 
                 if (item is LiveTvChannel tvChannel)
                 {
@@ -274,7 +308,8 @@ namespace Emby.Server.Implementations.Dto
             UserItemData? userData = null,
             List<Folder>? allCollectionFolders = null,
             Dictionary<Guid, int>? childCountBatch = null,
-            Dictionary<Guid, (int Played, int Total)>? playedCountBatch = null)
+            Dictionary<Guid, (int Played, int Total)>? playedCountBatch = null,
+            IReadOnlyDictionary<string, MusicArtist[]>? artistsBatch = null)
         {
             var dto = new BaseItemDto
             {
@@ -334,7 +369,7 @@ namespace Emby.Server.Implementations.Dto
                 AttachStudios(dto, item);
             }
 
-            AttachBasicFields(dto, item, owner, options);
+            AttachBasicFields(dto, item, owner, options, artistsBatch);
 
             if (options.ContainsField(ItemFields.CanDelete))
             {
@@ -907,7 +942,8 @@ namespace Emby.Server.Implementations.Dto
         /// <param name="item">The item.</param>
         /// <param name="owner">The owner.</param>
         /// <param name="options">The options.</param>
-        private void AttachBasicFields(BaseItemDto dto, BaseItem item, BaseItem? owner, DtoOptions options)
+        /// <param name="artistsBatch">Optional pre-fetched artist lookup shared across a batch of items.</param>
+        private void AttachBasicFields(BaseItemDto dto, BaseItem item, BaseItem? owner, DtoOptions options, IReadOnlyDictionary<string, MusicArtist[]>? artistsBatch = null)
         {
             if (options.ContainsField(ItemFields.DateCreated))
             {
@@ -1152,7 +1188,8 @@ namespace Emby.Server.Implementations.Dto
 
                 // Include artists that are not in the database yet, e.g., just added via metadata editor
                 // var foundArtists = artistItems.Items.Select(i => i.Item1.Name).ToList();
-                var artistsLookup = _libraryManager.GetArtists([.. hasArtist.Artists.Where(e => !string.IsNullOrWhiteSpace(e))]);
+                var artistsLookup = artistsBatch
+                    ?? _libraryManager.GetArtists([.. hasArtist.Artists.Where(e => !string.IsNullOrWhiteSpace(e))]);
 
                 dto.ArtistItems = hasArtist.Artists
                     .Where(name => !string.IsNullOrWhiteSpace(name))
@@ -1186,7 +1223,8 @@ namespace Emby.Server.Implementations.Dto
                 //    })
                 //    .ToList();
 
-                var albumArtistsLookup = _libraryManager.GetArtists([.. hasAlbumArtist.AlbumArtists.Where(e => !string.IsNullOrWhiteSpace(e))]);
+                var albumArtistsLookup = artistsBatch
+                    ?? _libraryManager.GetArtists([.. hasAlbumArtist.AlbumArtists.Where(e => !string.IsNullOrWhiteSpace(e))]);
 
                 dto.AlbumArtists = hasAlbumArtist.AlbumArtists
                     .Where(name => !string.IsNullOrWhiteSpace(name))
diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs
index d516bc0d13..5a96205b04 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs
@@ -3,7 +3,6 @@
 using System;
 using System.Collections.Generic;
 using System.Linq;
-using System.Linq.Expressions;
 using System.Threading.Tasks;
 using Jellyfin.Data.Enums;
 using Jellyfin.Database.Implementations;
@@ -125,53 +124,69 @@ public sealed partial class BaseItemRepository
             return GetLatestTvShowItems(context, baseQuery, filter, limit);
         }
 
-        // Find the top N group keys ordered by most recent DateCreated.
-        // Movies group by PresentationUniqueKey (alternate versions like 4K/1080p share a key).
-        // Music groups by Album.
-        Expression<Func<BaseItemEntity, bool>> groupKeyFilter;
-        Expression<Func<BaseItemEntity, string?>> groupKeySelector;
-
+        // Resolve the top N result item ids in a single SQL statement, ordered by the
+        // group's most recent DateCreated. Movies and music differ in what an "item"
+        // is, so the grouping shape is per-branch.
+        List<Guid> firstIds;
         if (collectionType is CollectionType.movies)
         {
-            groupKeyFilter = e => e.PresentationUniqueKey != null;
-            groupKeySelector = e => e.PresentationUniqueKey;
+            // Movies group by PresentationUniqueKey. Alternate versions (4K/1080p of the
+            // same movie) share that key, but they're already filtered out upstream by
+            // PrimaryVersionId IS NULL.
+            var topGroupItems = 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);
+
+            var idsQuery = filter.Limit.HasValue
+                ? topGroupItems.Take(filter.Limit.Value).Select(g => g.FirstId)
+                : topGroupItems.Select(g => g.FirstId);
+
+            firstIds = idsQuery.ToList();
         }
         else
         {
-            groupKeyFilter = e => e.Album != null;
-            groupKeySelector = e => e.Album;
+            // Music returns MusicAlbum entities, ordered by their latest track's
+            // DateCreated. Group by the MusicAlbum ancestor of each track via
+            // AncestorIds.
+            var musicAlbumType = _itemTypeLookup.BaseItemKindNames[BaseItemKind.MusicAlbum]!;
+
+            var topGroupItems =
+                from ancestor in context.AncestorIds
+                join track in baseQuery on ancestor.ItemId equals track.Id
+                join album in context.BaseItems on ancestor.ParentItemId equals album.Id
+                where album.Type == musicAlbumType
+                group track.DateCreated by album.Id into g
+                orderby g.Max() descending
+                select new { AlbumId = g.Key, MaxDate = g.Max() };
+
+            var idsQuery = filter.Limit.HasValue
+                ? topGroupItems.Take(filter.Limit.Value).Select(g => g.AlbumId)
+                : topGroupItems.Select(g => g.AlbumId);
+
+            firstIds = idsQuery.ToList();
         }
 
-        // Group by GroupKey, pick the latest item per group (correlated subquery: ORDER BY DateCreated DESC, Id DESC LIMIT 1),
-        // order groups by group max date, take the top N — all in a single SQL statement.
-        // ThenByDescending(Id) is the tiebreaker for deterministic ordering when items share a DateCreated.
-        var topGroupItems = baseQuery
-            .Where(groupKeyFilter)
-            .GroupBy(groupKeySelector)
-            .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);
-
-        var firstIdsQuery = filter.Limit.HasValue
-            ? topGroupItems.Take(filter.Limit.Value).Select(g => g.FirstId)
-            : topGroupItems.Select(g => g.FirstId);
+        // Load the result items by id. The order from firstIds is the group ordering
+        // we want; we re-apply it via dictionary lookup because for music the loaded
+        // album's own DateCreated may not match the album's latest-track date, so a
+        // SQL ORDER BY DateCreated wouldn't preserve it.
+        var itemsQuery = ApplyNavigations(
+            context.BaseItems.AsNoTracking().WhereOneOrMany(firstIds, e => e.Id),
+            filter);
 
-        var firstIds = firstIdsQuery.ToList();
-
-        // Single bound JSON / array parameter via WhereOneOrMany — keeps SQL small regardless of N.
-        var itemsQuery = context.BaseItems.AsNoTracking().WhereOneOrMany(firstIds, e => e.Id);
-        itemsQuery = ApplyNavigations(itemsQuery, filter);
-
-        return itemsQuery
-            .OrderByDescending(e => e.DateCreated)
-            .ThenByDescending(e => e.Id)
+        var itemsById = itemsQuery
             .AsEnumerable()
             .Select(w => DeserializeBaseItem(w, filter.SkipDeserialization))
             .Where(dto => dto != null)
-            .ToArray()!;
+            .ToDictionary(i => i!.Id);
+
+        return firstIds.Where(itemsById.ContainsKey).Select(id => itemsById[id]).ToArray()!;
     }
 
     /// <summary>