Newsletter takes hours to generate for large Emby collection

[biocatalyst]

Summary

I have a relatively large Emby Server with around 400 TV Series and around 2000 Movies. What I have been experiencing for a while now for quite a few versions of Ombi, is the newsletter process taking hours to run, I'm estimating on average close to 4 hours.

There's nothing appearing in the log that would explain this issue except for a couple of find errors on the themoviedb api.

So it leads me to consider that the newsletter process might be going through the entire collection instead of the newly added items since the last time the newsletter was generated.

Ombi Version

4.44.1

What platform(s) does this occur on?

Windows

What database are you using?

MySQL

Relevant log output

2024-07-26 06:02:25.635 +10:00 [INF] Default Quartz.NET properties loaded from embedded resource file
2024-07-26 06:02:25.692 +10:00 [INF] Initialized Scheduler Signaller of type: Quartz.Core.SchedulerSignalerImpl
2024-07-26 06:02:25.693 +10:00 [INF] Quartz Scheduler created
2024-07-26 06:02:25.693 +10:00 [INF] RAMJobStore initialized.
2024-07-26 06:02:25.694 +10:00 [INF] Quartz Scheduler 3.6.2.0 - 'DefaultQuartzScheduler' with instanceId 'NON_CLUSTERED' initialized
2024-07-26 06:02:25.694 +10:00 [INF] Using thread pool 'Quartz.Simpl.DefaultThreadPool', size: 10
2024-07-26 06:02:25.694 +10:00 [INF] Using job store 'Quartz.Simpl.RAMJobStore', supports persistence: False, clustered: False
2024-07-26 06:02:25.696 +10:00 [INF] JobFactory set to: Ombi.Schedule.IoCJobFactory
2024-07-26 06:02:25.766 +10:00 [INF] Scheduler DefaultQuartzScheduler_$_NON_CLUSTERED started.
2024-07-26 15:00:31.679 +10:00 [ERR] StatusCode: NotFound, Reason: Not Found, RequestUri: http://api.themoviedb.org/3/find/?api_key=b8eabaf5608b88d0298aa189dd90bf00&external_source=imdb_id
2024-07-26 15:05:58.549 +10:00 [ERR] StatusCode: NotFound, Reason: Not Found, RequestUri: http://api.themoviedb.org/3/find/?api_key=b8eabaf5608b88d0298aa189dd90bf00&external_source=imdb_id
2024-07-26 15:06:47.498 +10:00 [ERR] StatusCode: NotFound, Reason: Not Found, RequestUri: http://api.themoviedb.org/3/find/?api_key=b8eabaf5608b88d0298aa189dd90bf00&external_source=imdb_id

[biocatalyst]

Hi there,

I don't know if this will help or not, but I do data engineering in my career, here's a bunch of SQL queries I ran on the MySQL DB that will produce the moviesContents and episodes arrays (as found in NewsletterJob.cs), taking into account what has been inserted already into the RecentlyAddedLog Table:

moviesContents
SELECT C.Type AS RecentlyAddedType, C.TheMovieDbId, C.Title FROM EmbyContent C
LEFT OUTER JOIN RecentlyAddedLog RAL ON RAL.ContentId=C.TheMovieDbId AND RAL.ContentType=0
WHERE C.Type=0
AND C.TheMovieDbId IS NOT NULL
AND RAL.Id IS NULL
ORDER BY REPLACE(REPLACE(REPLACE(C.Title,'The ',''),'A ',''),'An ','')

episodes
SELECT C.Type as RecentlyAddedType, E.TvDbId, E.SeasonNumber, E.EpisodeNumber, C.Title
FROM EmbyContent C
INNER JOIN EmbyEpisode E ON E.ParentId=C.EmbyId AND E.TvDbId IS NOT NULL
LEFT OUTER JOIN RecentlyAddedLog RAL ON RAL.ContentId=C.TvDbId AND RAL.ContentType=1
WHERE C.Type=1
AND RAL.Id IS NULL
ORDER BY REPLACE(REPLACE(REPLACE(C.Title,'The ',''),'A ',''),'An ',''),E.SeasonNumber,E.EpisodeNumber