Speeding up Database Queries

[GothsterC 50]

Hello,

  I've been trying to improve my performance of my Emby system for some time.  Currently I am focused on the slow SQL queries.  Things take a long time to load (over Gigabit Ethernet) and when I check the logs, I see that every SQL query is labeled as slow, for example:

 

2017-07-17 13:12:12.5445 Debug SqliteItemRepository: GetItemList query time (slow): 77039ms. Query: select type,data,StartDate,EndDate,ChannelId,IsMovie,IsSports,IsKids,IsSeries,IsLive,IsNews,IsPremiere,EpisodeTitle,IsRepeat,CommunityRating,IndexNumber,IsHD,Name,Path,PremiereDate,Overview,ParentIndexNumber,ProductionYear,OfficialRating,RunTimeTicks,DateModified,guid,ParentId,Audio,ExternalServiceId,IsInMixedFolder,TrailerTypes,PrimaryVersionId,Album,CriticRating,IsVirtualItem,SeriesName,SeasonName,SeasonId,SeriesId,ProviderIds,Images,TotalBitrate,ExtraType,Artists,AlbumArtists,ExternalId,UserData.UserId,UserData.lastPlayedDate,UserData.playbackPositionTicks,UserData.playcount,UserData.isFavorite,UserData.played,UserData.rating from TypedBaseItems A left join UserData on UserDataKey=UserData.Key And (UserId=@UserId) where playbackPositionTicks > 0 AND IsVirtualItem=@IsVirtualItem AND MediaType=@MediaTypes AND (TopParentId in ('8e7b9f7ebe55a2e049be92d66173ac05','e8ab706f63f41d470a67b38d3f7fa3ca','f6014c2c930261537bc6699393c18365','6961892d68d4f41e54a2e9e3625e6329','93bb1909e5fbb880de92ce3c0c4086e6','6062a624848b6b41556b52164f7cd1ca','ec729abd540287c7af847332b33d9189','d1e1f33940296473c434478983d925fd','439104b88e65c1ee22edddf72836771c','5ae26a5890e01800388f81892323c9c0','711cc2179ee4eec6bd8c137287cfdfe7','da1531ddd3e7a2a1d91306f97261bfd8','3166d422e586b8455614047caeac8ba2','bacfe2009cc9f1bc8deec0fca003389c','5e1edc2b7d798b0320d7a92e68633e9a','57133b37d68bf33141eeb929d83567dd','0edda7683e840f9a1de233b23b4de865','70a867f27fb63d9c7456286c93aa04b6','195b83a258d9b2e6cee9b13e5624d586','e520a65cb8260baad95a1dd5687598b0','09df5616edf71a910cbc51971f9960f7','44c664a11356b52dba94f88cfa760bec','71d123aa30c296e09a596397321e33c3','734dbcd08814f5e72fe062cdccdbb577','0bca0826b867d8c44a8a0f312d894327','91fd9940588b093b8036664acc80b6c8','8c422670c686040ce8d48854be336948','4e33103d0083226b4163613f041d9745','24a4d8e075d8081a5e345a2489398246','fa6dde3bb639f859a96c24b6e956dd12','229566bf961d0d4878379e64e02064bf','d20c67b91a097911d55143d7839c16f8','736322b222b103d7a494f0d9709018d5','e328800a280f9429d2f24f671202e682','d3f21e4798a3bb4ff435593c54158edb','562066992e0639a64d4f55d40828e57f','d0bdc6ad53d983bb738468768de63bb7','80a841e695dff30e2d8d1a44a288bc94','cddcf570335ca8b34a168cbfbbd3872f','a189de9fb21c7b672751d3350f120db0','d2bdd03c5f9ddc41b865a3733ac22011','d2bdd03c5f9ddc41b865a3733ac22011','a189de9fb21c7b672751d3350f120db0','4c5175ae77d5671dddad82cae742f288','38967b15eae5af3663339cb277bf1046','ce586a0db621778212cfea13ce761a69','841c326518bc310cf2097a43579358ff','9ef3633a2653de85fee11bc5b5f146bf','9df4efab1f9566a6088195a3f6029855','4f72d19e53d44af6a8091b4d0bdaaee8','ff36d5cf4654676d9469af7009cd11ad')) Group by PresentationUniqueKey ORDER BY LastPlayedDate DESC LIMIT 50

 

2017-07-17 13:12:12.6611 Info HttpServer: HTTP Response 200 to 10.10.10.162. Time: 94387ms. http://10.10.10.4:8096/emby/Shows/NextUp?Limit=50&UserId=998c6375685dea40a89bd0cf50bb0f45&ImageTypeLimit=1&Fields=PrimaryImageAspectRatio%2COverview%2CDateCreated&format=json

2017-07-17 13:12:12.7336 Debug SqliteItemRepository: GetItemList query time (slow): 64267ms. Query: select type,data,StartDate,EndDate,ChannelId,IsMovie,IsSports,IsKids,IsSeries,IsLive,IsNews,IsPremiere,EpisodeTitle,IsRepeat,CommunityRating,CustomRating,IndexNumber,IsLocked,PreferredMetadataLanguage,PreferredMetadataCountryCode,IsHD,ExternalEtag,DateLastRefreshed,Name,Path,PremiereDate,Overview,ParentIndexNumber,ProductionYear,OfficialRating,HomePageUrl,DisplayMediaType,ForcedSortName,RunTimeTicks,DateCreated,DateModified,guid,Genres,ParentId,Audio,ExternalServiceId,IsInMixedFolder,DateLastSaved,LockedFields,Studios,Tags,TrailerTypes,OriginalTitle,PrimaryVersionId,DateLastMediaAdded,Album,CriticRating,IsVirtualItem,SeriesName,SeasonName,SeasonId,SeriesId,PresentationUniqueKey,InheritedParentalRatingValue,InheritedTags,ExternalSeriesId,Tagline,Keywords,ProviderIds,Images,ProductionLocations,ThemeSongIds,ThemeVideoIds,TotalBitrate,ExtraType,Artists,AlbumArtists,ExternalId,SeriesPresentationUniqueKey from TypedBaseItems A where ParentId=@ParentId

 

 

Are there any indexes that can added or something that can be done to speed the database up?  I am running the latest Windows server version on a Windows 10 machine with Intel i7-2600 3.4 GHz CPU and 16 GB of RAM.  The only other thing running on the machine is Logitech Media Server, so the machine and network shouldn't be the issue.  

 

Thanks. 

 

[Luke 42085]

Please attach an emby server log, thanks.

[GothsterC 50]

Here you go.

Emby Server Log.txt

[Luke 42085]

@gothserc, plugins for Emby run in-process and will affect performance. You are made aware of this when you install a plugin. You can increase the performance of your Emby Server by removing all plugins except for Backup and Trailers.

 

You can increase performance even more with a fresh install and limiting yourself to just those two plugins.

[GothsterC 50]

Thanks.  I will start by disabling the plugins except for the server backups and trailers, but I also want to keep CoverArt.  That is an important one for me.

[bfir3 117]

I regularly see these slow messages as well, though not of the same duration as @@GothsterC mine are usually around 10-15 seconds. Would be lovely to improve the performance for these queries. Especially the ones for the home page. If latest items are that taxing, couldn't we just use a server side cache and update it whenever the server scans in new items and/or on an interval.

[GothsterC 50]

I removed some plugins including Auto Box Sets, Genre Cleaner and Rotten Tomatoes.  I'm seeing much better performance now.  I wonder if Rotten Tomatoes was constantly going out and looking things up which could slow things down.

[Luke 42085]

Thanks for the feedback.