I decided to take full advantage of my free trial of Azure and switched over to Azure SQL.
Azure SQL has a neat feature called "Query Performance Insight", which lists how many times queries have been ran, CPU usage, etc.
What really stood out to me was how many times some queries have been ran. I'm probably the only person accessing my site, and the following query:
(@__pc_CategoryId_0 int)SELECT TOP(1) [c].[Id], [c].[Description], [c].[ImgSrc], [c].[LastUpdated], [c].[ParentId], [c].[ProfileId], [c].[Rank], [c].[Slug], [c].[Title] FROM [Categories] AS [c] WHERE [c].[Id] = @__pc_CategoryId_0
has been executed about 50,000 times in the span of a few hours.
I'm not sure what exactly is causing this. My public APIs don't even contain the looping through categories that happens in BlogController.SinglePublication
. The categories should really only be loaded once per user session (I do some jQuery queries to get the appropriate category name in a post view instead of relying on DB queries).
My best guess as to what is happening is the deep loading of the models. For example, a BlogPost
contains a List<PostCategory>
and Profile
. Each PostCategory
contains a BlogPost
and Category
, while a Profile
contains a List<BlogPost>
. I don't know if all of these get populated for each request, but I don't know how else to explain how I got 50k query executions by myself (in what was probably around 100 actual requests). My site has 6 categories, and 63 posts.
Obviously, 50k queries isn't very bad - but its pretty bad for my site. I'm guessing if I had more categories, more posts, and lots of users, the workload might become too much very quickly.