Possible high database usage

[murst]

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.

[murst]

I went ahead and added lazy loading for Categories in PostListItem.

Its a pretty small change, but I think it will solve the issue in my API. I'll monitor my DB and see if there's any other queries that stand out.

murst@0f71ea6

Let me know what you think.

[rxtur]

Cool, I'll be looking into this next week but if this fixes an issue with minimum code change that would be great.

[rxtur]

Looking at performance insight on demo site, I see 20-30 executions per hour for this query. So this is probably related to API call doing extra work compared to "internal". I've seen issues with AJAX serialization similar to this one, when "normal" call have no issues but API on the client ran into overflow with circular reference in relations.

[murst]

If this is something that comes into play during serialization, maybe a [Newtonsoft.Json.JsonIgnore] attribute will be sufficient on the Categories property in PostListItem.

[murst]

I'm pretty sure I did this already, but I'll run another test case and null the Categories before returning from the API call. If this is due to serialization, then the Categories should just return a null instead of querying the database.

[murst]

I removed the lazy loading and I racked up 500 calls to that query in a few page requests. The following:
var posts = _db.BlogPosts.Find(p => p.Published > DateTime.MinValue, pager);
seems to always call hit the database to fill the Categories (inside the private GetItem(BlogPost post) method in PostRepository.cs).

If you put a breakpoint inside the GetCategories(BlogPost post) method in PostRepository, you should see it being called every time the Index page is loaded. Perhaps the test server posts don't have many categories associated with them?

[murst]

An alternative would be to have the BlogPost model contain a List<Category> instead of a List<PostCategory>. That way the post and its categories could be obtained via a single query to the database. The code as it is right now is looping through the List<PostCategory> and manually doing a join to Category, one category at a time. A single query with inner joins should be much more efficient than a foreach loop.

[rxtur]

I don't think we need categories for a post list, they only should be needed for single post. Post list very light weight, it does not ever return post content for this very reason. I'll sort this out later tonight.

[murst]

Since the categories aren't part of PostListItem anymore, its probably safe to close.