AI OnAI Off
Table [tblNotificationSubscription] grows and causes high load on SQL server
I found that we could remove all notification subscriptions for this specific user at the end of the scheduled job like this:
var subscriptionService = ServiceLocator.Current.GetInstance<ISubscriptionService>();
var notificationUser = new NotificationUser(scheduledJobsUser);
subscriptionService.ClearUserAsync(notificationUser);Does any one have any thoughts on this?
May 20, 2019 15:51
Hi Erik we have the exact same issue. There are several jobs that runs frequently. One syncs 11000 users every 4th hour, one is importing files from disk to epi-generic media and the third gets files from a service all jobs save and/or publish content. We got the same poor SQL performance and I looked in the SQL Server and noticed that the table tblNotificationSubscription had 35 000 000 rows.
We truncated the table and has implemented the code you written (as a proposition from Optimizely support).
You might experience some other tables having a lot of rows also and these are dbo.tblActivityLog, dbo.tblActivityArchive we got the information to run
following SP : EXEC [dbo].[netActivityLogTruncateArcive]
@MaxRows = 1000000, @CreatedBefore = '2021/09/01'
I also asked what these tables are used for and got the answer it is for 3-party users and API integrations where there is possible to check who has saved/published items in the application, distinguished by name I should think.
Nov 30, 2021 10:27
I've seen a lot performance issues around tblNotificationSubscription, seems like Optimizely should do more of the development around this with thousands of editor users in the database.
The last problem I chased around this was also dependent on the order of where the current user was in a list. There was some poorly written code that iterated a list and fired db calls for every row and then stopped when it reached the specific user. So if the user was among the last in a list of thousands the load time was massive. We did a temporary hack/fix with sort of a short circuit in a stored procedure: https://world.optimizely.com/forum/developer-forum/CMS/Thread-Container/2018/12/loading-edit-mode-very-slowly-every-once-in--a-while/
Dec 02, 2021 6:45
Hi,
After upgrading to EpiServer CMS 11 lately (1.5 month ago) we have started to get SQL performance problem on our production servers.
The SQL statement causing this seems to be:
SELECT @SubscriptionCount = COUNT(*)FROM [dbo].[tblNotificationSubscription]WHERE UserName = @UserName AND SubscriptionKey = @key And Active=These requests happends often and the average time was ~13 seconds.
When i looked in [tblNotificationSubscription] it contained ~ 2.5 million rows and was growing.
We cleared the table and the SQL performance problems where gone.
Now five days later the table contains 250k rows and is growing fast.
We don't have any performance problem yet but I guess they will be back.
I can se that rows are added to the table everytime an editor is visiting a page in edit mode.
Is that the correct behaviour?
Our site has multiple scheduled jobs which modifies the content and I think that this also adds rows to this table.
These jobs is running as a specific user and almost 99% of the rows in the table has this UserName.
Does any one know if this is a bug or if there is something we can do to handle this?
Is it possible to disasble notofocations for a specific user or in a scheduled job?
What is the purpose of this table?
/Erik Jonsson