Performance issues when shuffling library with specific filters

[D3lt4y 2]

Hi!

Since upgrading to Emby Server 4.8 I have noticed significant performance issues when using the "Play" and "Shuffle" buttons on the library level:

With some testing I have narrowed the issue down to the use of specific filters. When filtering by Genres, Studios or Tags and then hitting play or shuffle the whole server starts to hang. Sometimes just for a few seconds, but depending on the number of results in can take several minutes for the server to recover. In some cases I even had to kill the process entirely and restart Emby.

When hitting play or shuffle without any filters (or with other filters like Year that don't seem to be affected by this issue) the movie starts instantly.

The debug logs seem to indicate that this is caused by a specific sqlite query:

2024-04-07 12:47:39.115 Debug SqliteItemRepository: ItemsService.GetItems.GetItems query time (slow 6x): 24717ms. Query: select count(*) OVER() AS TotalRecordCount,A.Id,A.EndDate,A.Name,A.Path,A.ProductionYear,A.RunTimeTicks,A.ParentId,A.Images,UserDatas.IsFavorite,UserDatas.Played,UserDatas.PlaybackPositionTicks,UserDatas.AudioStreamIndex,UserDatas.SubtitleStreamIndex,(Select ShareLevel from UserItemShares join AncestorIds2 on AncestorIds2.AncestorId=UserItemShares.ItemId where UserItemShares.UserId=1 and UserItemShares.ShareLevel not null and AncestorIds2.ItemId=A.Id order by Distance limit 1) as ShareLevel from (select A.id, RowNumber from (select id,ROW_NUMBER(*) over(ORDER BY RANDOMSORTVALUE(MediaItemsInnerProjectToMedia.Id || MediaItemsInnerProjectToMedia.SortName, 4678) DESC) as RowNumber from MediaItems MediaItemsInnerProjectToMedia Join ItemLinks2 ItemLinksFilters on ItemLinksFilters.ItemId=MediaItemsInnerProjectToMedia.Id and ItemLinksFilters.LinkedId=434 where MediaItemsInnerProjectToMedia.Type=5 AND MediaItemsInnerProjectToMedia.Id in (select ItemId from AncestorIds2 where AncestorId=4 ) Group by MediaItemsInnerProjectToMedia.Id ORDER BY RANDOMSORTVALUE(MediaItemsInnerProjectToMedia.Id || MediaItemsInnerProjectToMedia.SortName, 4678) DESC ) as InnerQueryItems join MediaItems A on A.Id = InnerQueryItems.Id or A.Id in (Select ItemId from ItemLinks2 where ItemLinks2.LinkedId=InnerQueryItems.Id and ItemLinks2.Type in (0,1,5,6,7)) or A.Id in (Select ItemId from AncestorIds2 where AncestorIds2.AncestorId=InnerQueryItems.Id) ) as InnerQueryItems join MediaItems A on A.Id = InnerQueryItems.Id or A.Id in (Select ItemId from ItemLinks2 where ItemLinks2.LinkedId=InnerQueryItems.Id and ItemLinks2.Type in (0,1,5,6,7)) or A.Id in (Select ItemId from AncestorIds2 where AncestorIds2.AncestorId=InnerQueryItems.Id) left join UserDatas on A.UserDataKeyId=UserDatas.UserDataKeyId And UserDatas.UserId=1 where A.Type=5 AND Coalesce(ShareLevel, 0) > 0 Group by A.PresentationUniqueKey ORDER BY RANDOM() ASC LIMIT 300

I already tried most of the standard database troubleshooting tips suggested on this forum like increasing the cache size and vacuuming the database, but they had no measurable effect on these query times.

As mentioned above this issue only started happening once I upgraded to 4.8. Previously movies would always start instantly regardless of the filters selected.

I have attached the full log file for one example of this slowdown.

Note that this logfile is from a truenas server running off an HDD, but I have also replicated the issue on another emby instance running on a windows machine off an SSD. So it doesn't seem to be related to the platform or hardware.

embyserver.txt

[viking19 24]

I see the same thing (long query time) and also with music on 4.8.3 and at least 4.9.0.9+ servers as per Latest update broke Genre even more.

[Luke 37100]

HI, we're looking into it. Thanks for reporting.