Merge pull request #16062 from Shadowghost/perf-rebased

Query Performance Improvements

1 files changed, 54 insertions, 33 deletions

diff --git a/Emby.Server.Implementations/Library/UserDataManager.cs b/Emby.Server.Implementations/Library/UserDataManager.cs
index 72c8d7a9d2..1281f1587f 100644
--- a/Emby.Server.Implementations/Library/UserDataManager.cs
+++ b/Emby.Server.Implementations/Library/UserDataManager.cs
@@ -177,53 +177,74 @@ namespace Emby.Server.Implementations.Library
             };
         }
 
-        private UserItemData? GetUserData(User user, Guid itemId, List<string> keys)
+        /// <inheritdoc />
+        public Dictionary<Guid, UserItemData> GetUserDataBatch(IReadOnlyList<BaseItem> items, User user)
         {
-            var cacheKey = GetCacheKey(user.InternalId, itemId);
+            var result = new Dictionary<Guid, UserItemData>(items.Count);
+            var itemsNeedingQuery = new List<(BaseItem Item, List<string> Keys)>();
 
-            if (_cache.TryGet(cacheKey, out var data))
+            foreach (var item in items)
             {
-                return data;
-            }
-
-            data = GetUserDataInternal(user.Id, itemId, keys);
-
-            if (data is null)
-            {
-                return new UserItemData()
+                var cacheKey = GetCacheKey(user.InternalId, item.Id);
+                if (_cache.TryGet(cacheKey, out var cachedData))
                 {
-                    Key = keys[0],
-                };
+                    result[item.Id] = cachedData;
+                }
+                else
+                {
+                    var userData = item.UserData?.Where(e => e.UserId.Equals(user.Id)).Select(Map).FirstOrDefault();
+                    if (userData is not null)
+                    {
+                        result[item.Id] = userData;
+                        _cache.AddOrUpdate(cacheKey, userData);
+                    }
+                    else
+                    {
+                        var keys = item.GetUserDataKeys();
+                        itemsNeedingQuery.Add((item, keys));
+                    }
+                }
             }
 
-            return _cache.GetOrAdd(cacheKey, _ => data);
-        }
-
-        private UserItemData? GetUserDataInternal(Guid userId, Guid itemId, List<string> keys)
-        {
-            if (keys.Count == 0)
+            if (itemsNeedingQuery.Count == 0)
             {
-                return null;
+                return result;
             }
 
-            using var context = _repository.CreateDbContext();
-            var userData = context.UserData.AsNoTracking().Where(e => e.ItemId == itemId && keys.Contains(e.CustomDataKey) && e.UserId.Equals(userId)).ToArray();
-
-            if (userData.Length > 0)
+            // Build a single query for all missing items
+            var allItemIds = itemsNeedingQuery.Select(x => x.Item.Id).ToList();
+            var allKeys = itemsNeedingQuery.SelectMany(x => x.Keys).Distinct().ToList();
+            if (allKeys.Count > 0)
             {
-                var directDataReference = userData.FirstOrDefault(e => e.CustomDataKey == itemId.ToString("N"));
-                if (directDataReference is not null)
+                using var context = _repository.CreateDbContext();
+                var userDataArray = context.UserData
+                    .AsNoTracking()
+                    .Where(e => e.UserId.Equals(user.Id))
+                    .WhereOneOrMany(allItemIds, e => e.ItemId)
+                    .WhereOneOrMany(allKeys, e => e.CustomDataKey)
+                    .ToArray();
+
+                var userDataByItem = userDataArray.GroupBy(e => e.ItemId).ToDictionary(g => g.Key, g => g.ToArray());
+                foreach (var (item, keys) in itemsNeedingQuery)
                 {
-                    return Map(directDataReference);
-                }
+                    UserItemData userData;
+                    if (userDataByItem.TryGetValue(item.Id, out var itemUserData) && itemUserData.Length > 0)
+                    {
+                        var directDataReference = itemUserData.FirstOrDefault(e => e.CustomDataKey == item.Id.ToString("N"));
+                        userData = directDataReference is not null ? Map(directDataReference) : Map(itemUserData.First());
+                    }
+                    else
+                    {
+                        userData = new UserItemData { Key = keys.Count > 0 ? keys[0] : string.Empty };
+                    }
 
-                return Map(userData.First());
+                    result[item.Id] = userData;
+                    var cacheKey = GetCacheKey(user.InternalId, item.Id);
+                    _cache.AddOrUpdate(cacheKey, userData);
+                }
             }
 
-            return new UserItemData
-            {
-                Key = keys.Last()!
-            };
+            return result;
         }
 
         /// <summary>