Fix performance

3 files changed, 52 insertions, 15 deletions

diff --git a/Emby.Server.Implementations/Library/UserDataManager.cs b/Emby.Server.Implementations/Library/UserDataManager.cs
index 4fda853bb1..bedaacee33 100644
--- a/Emby.Server.Implementations/Library/UserDataManager.cs
+++ b/Emby.Server.Implementations/Library/UserDataManager.cs
@@ -259,15 +259,53 @@ namespace Emby.Server.Implementations.Library
             ArgumentNullException.ThrowIfNull(user);
 
             var result = new Dictionary<Guid, VersionResumeData>();
+
+            // Candidate primaries: a directly queried version (PrimaryVersionId set) keeps its own data.
+            // Linked alternates are already known in memory; only the local-alternate existence check
+            // would otherwise hit the database (one query per item via Video.HasLocalAlternateVersions),
+            // so collect those ids and resolve them all in a single query below.
+            List<Video>? candidates = null;
+            List<Guid>? localProbeIds = null;
+            foreach (var item in items)
+            {
+                if (item is not Video video || video.PrimaryVersionId.HasValue)
+                {
+                    continue;
+                }
+
+                (candidates ??= []).Add(video);
+
+                if (video.LinkedAlternateVersions.Length == 0)
+                {
+                    (localProbeIds ??= []).Add(video.Id);
+                }
+            }
+
+            if (candidates is null)
+            {
+                return result;
+            }
+
+            HashSet<Guid>? withLocalAlternates = null;
+            if (localProbeIds is not null)
+            {
+                using var dbContext = _repository.CreateDbContext();
+                withLocalAlternates = dbContext.LinkedChildren
+                    .Where(lc => lc.ChildType == Jellyfin.Database.Implementations.Entities.LinkedChildType.LocalAlternateVersion
+                        && localProbeIds.Contains(lc.ParentId))
+                    .Select(lc => lc.ParentId)
+                    .Distinct()
+                    .ToHashSet();
+            }
+
             List<(Guid PrimaryId, IReadOnlyList<Video> Versions)>? versionGroups = null;
             List<BaseItem>? allVersions = null;
 
-            foreach (var item in items)
+            foreach (var video in candidates)
             {
-                // Only primary items aggregate over their versions; a directly queried version keeps its own data.
-                if (item is not Video video
-                    || video.PrimaryVersionId.HasValue
-                    || (video.LinkedAlternateVersions.Length == 0 && !video.HasLocalAlternateVersions))
+                // Only items that actually have alternate versions aggregate over them.
+                if (video.LinkedAlternateVersions.Length == 0
+                    && (withLocalAlternates is null || !withLocalAlternates.Contains(video.Id)))
                 {
                     continue;
                 }
@@ -278,7 +316,7 @@ namespace Emby.Server.Implementations.Library
                     continue;
                 }
 
-                (versionGroups ??= []).Add((item.Id, versions));
+                (versionGroups ??= []).Add((video.Id, versions));
                 (allVersions ??= []).AddRange(versions);
             }
 
diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
index a9856f2e76..96d6d2eaff 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
@@ -546,9 +546,8 @@ public sealed partial class BaseItemRepository
                 // Non-series items: resumable if the item or any of its alternate versions has
                 // PlaybackPositionTicks > 0. Alternate versions (PrimaryVersionId set) are excluded
                 // from the base query, so coalesce their progress onto the primary's id.
-                var resumableMovieIds = context.BaseItems
-                    .Where(bi => bi.UserData!.Any(ud => ud.UserId == userId && ud.PlaybackPositionTicks > 0))
-                    .Select(bi => bi.PrimaryVersionId ?? bi.Id);
+                var resumableMovieIds = inProgress
+                    .Join(context.BaseItems, ud => ud.ItemId, bi => bi.Id, (ud, bi) => bi.PrimaryVersionId ?? bi.Id);
 
                 baseQuery = baseQuery.Where(e =>
                     (e.Type == seriesTypeName && resumableSeriesIds.Contains(e.Id) == isResumable)
@@ -559,9 +558,8 @@ public sealed partial class BaseItemRepository
                 // Resumable if the item or any of its alternate versions has PlaybackPositionTicks > 0.
                 // Alternate versions (PrimaryVersionId set) are excluded from the base query, so
                 // coalesce their progress onto the primary's id.
-                var resumableMovieIds = context.BaseItems
-                    .Where(bi => bi.UserData!.Any(ud => ud.UserId == userId && ud.PlaybackPositionTicks > 0))
-                    .Select(bi => bi.PrimaryVersionId ?? bi.Id);
+                var resumableMovieIds = inProgress
+                    .Join(context.BaseItems, ud => ud.ItemId, bi => bi.Id, (ud, bi) => bi.PrimaryVersionId ?? bi.Id);
                 baseQuery = baseQuery.Where(e => resumableMovieIds.Contains(e.Id) == isResumable);
             }
 
diff --git a/tests/Jellyfin.Server.Implementations.Tests/Item/AlternateVersionQueryTranslationTests.cs b/tests/Jellyfin.Server.Implementations.Tests/Item/AlternateVersionQueryTranslationTests.cs
index e6e591a20e..823ff566a7 100644
--- a/tests/Jellyfin.Server.Implementations.Tests/Item/AlternateVersionQueryTranslationTests.cs
+++ b/tests/Jellyfin.Server.Implementations.Tests/Item/AlternateVersionQueryTranslationTests.cs
@@ -50,9 +50,10 @@ public sealed class AlternateVersionQueryTranslationTests : IDisposable
         {
             // Mirrors the resumable filter in BaseItemRepository.TranslateQuery: progress on any
             // version coalesces onto the primary's id.
-            var resumableMovieIds = ctx.BaseItems
-                .Where(bi => bi.UserData!.Any(ud => ud.UserId == userId && ud.PlaybackPositionTicks > 0))
-                .Select(bi => bi.PrimaryVersionId ?? bi.Id);
+            var inProgress = ctx.UserData
+                .Where(ud => ud.UserId == userId && ud.PlaybackPositionTicks > 0);
+            var resumableMovieIds = inProgress
+                .Join(ctx.BaseItems, ud => ud.ItemId, bi => bi.Id, (ud, bi) => bi.PrimaryVersionId ?? bi.Id);
 
             // Scope to the seeded items; EnsureCreated also seeds a placeholder row.
             var seededIds = new[] { primaryId, otherId };