News publishing takes several minutes
Hi Everyone,
I am experiencing an issue with News - every time I publish news item, it takes about 2 minutes to get published.
I started to dig into the issue a bit with SQL Server Profiler and found out that publishing causes ~16000 queries to be executed against the database. Most of them are simple selects from sf_commnt table executed in the loop (!) by consecutive calls of sp_execute with sp_prepexec called earlier to set up the select. sp_execute in this case seems to be called for every single record in sf_commnt (I checked several calls and content_id's match with parameter passed into sp_execute).
We had an issue with News - some items had comments open and sneaky spamers had time to fill them with trash (~3000 comments). We don't use comments so I deleted all of them from the system (according to this www.sitefinity.com/.../blog-comment-spam---thousands-to-delete I had to delete them manually), but sf_commnt still has all the records (>15000) and News publishing still makes ~16000 queries.
Current News publishing performance is unacceptable and I think the reason behind this is this huge number of DB requests. What should I do to get rid of those additional 15000+ DB queries to sf_commnt during News publish? I don't need comments, but deleting them does not help. I also tried wiping them out physically from the sf_commnt table but it did not help either. Should I wipe them and also have database cache to be rebuilt somehow? Please advice.
FYI I'm running Sitefinity 9.1.
Thank you!
In case someone will meet similar issue - it looks like core storage for comments are these 4 tables:
I don't have anything referenced by foreign keys that would link comments to actual content items (news, pages, blog posts, etc.), so in my case deleting everything from these tables seem to have resolved the issue. No comments - no delays in news publishing!