wjones
7eb2b445cb
- Major query performance boost: replaced correlated subqueries with DistinctBy() in BaseItemRepository, added episode deduplication index, and increased default command timeout to 120s. - Fixed LibraryMonitor shutdown race: added disposal checks and exception handling in FileRefresher to prevent ObjectDisposedException. - Added PowerShell and SQL utilities for fixing Linux paths in config files and database; new scripts for WebDir and path migration. - Auto-fix for WebDir in startup.json on load; new helper scripts and docs. - Added automated weekly DB performance monitoring scripts and reporting. - Expanded documentation and README for all fixes, scripts, and migration steps. - Updated SQL scripts for index creation and diagnostics; improved output handling. - Updated db-config defaults and added new diagnostic/report files.
19 lines
43 KiB
Plaintext
19 lines
43 KiB
Plaintext
query
|
|
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
|
SELECT b."Id", b."Album", b."AlbumArtists", b."Artists", b."Audio", b."ChannelId", b."CleanName", b."CommunityRating", b."CriticRating", b."CustomRating", b."Data", b."DateCreated", b."DateLastMediaAdded", b."DateLastRefreshed", b."DateLastSaved", b."DateModified", b."EndDate", b."EpisodeTitle", b."ExternalId", b."ExternalSeriesId", b."ExternalServiceId", b."ExtraIds", b."ExtraType", b."ForcedSortName", b."Genres", b."Height", b."IndexNumber", b."InheritedParentalRatingSubValue", b."InheritedParentalRatingValue", b."IsFolder", b."IsInMixedFolder", b."IsLocked", b."IsMovie", b."IsRepeat", b."IsSeries", b."IsVirtualItem", b."LUFS", b."MediaType", b."Name", b."NormalizationGain", b."OfficialRating", b."OriginalTitle", b."Overview", b."OwnerId", b."ParentId", b."ParentIndexNumber", b."Path", b."PreferredMetadataCountryCode", b."PreferredMetadataLanguage", b."PremiereDate", b."PresentationUniqueKey", b."PrimaryVersionId", b."ProductionLocations", b."ProductionYear", b."RunTimeTicks", b."SeasonId", b."SeasonName", b."SeriesId", b."SeriesName", b."SeriesPresentationUniqueKey", b."ShowId", b."Size", b."SortName", b."StartDate", b."Studios", b."Tagline", b."Tags", b."TopParentId", b."TotalBitrate", b."Type", b."UnratedType", b."Width", u."ItemId", u."UserId", u."CustomDataKey", u."AudioStreamIndex", u."IsFavorite", u."LastPlayedDate", u."Likes", u."PlayCount", u."PlaybackPositionTicks", u."Played", u."Rating", u."RetentionDate", u."SubtitleStreamIndex"\r+
|
|
FROM library."BaseItems" AS b\r +
|
|
LEFT JOIN library."UserData" AS u ON b."Id" = u."ItemId"\r +
|
|
WHERE b."Id" IN (\r +
|
|
SELECT (\r +
|
|
SELECT b1."Id"\r +
|
|
FROM library."BaseItems" AS b1\r +
|
|
WHERE b1."Type" = $3 AND b1."IsVirtualItem" = $1 AND (b1."TopParentId" = ANY ($2) OR (b1."TopParentId" IS NULL AND array_position($2, $4) IS NOT NULL)) AND (b0."PresentationUniqueKey" = b1."PresentationUniqueKey" OR (b0."PresentationUniqueKey" IS NULL AND b1."PresentationUniqueKey" IS NULL))\r +
|
|
LIMIT $5)\r +
|
|
FROM library."BaseItems" AS b0\r +
|
|
WHERE b0."Type" = $6 AND b0."IsVirtualItem" = $1 AND (b0."TopParentId" = ANY ($2) OR (b0."TopParentId" IS NULL AND array_position($2, $7) IS NOT NULL))\r +
|
|
GROUP BY b0."PresentationUniqueKey"\r +
|
|
)\r +
|
|
ORDER BY b."SortName", b."Id", u."ItemId", u."UserId"
|
|
(1 row)
|
|
|