I run into a really interesting article about performance optimizations with EF Core and I thought that it deserve a second & third look. You might have noticed that I have been putting a lot of emphasis on performance and I had literally spent years on optimizing relational database access patterns, including building a profiler dedicated for inspecting what an OR/M is doing. I got the source and run the application.
I have a small bet with myself, saying that in any application using a relational database, I’ll be able to find a SELECT N+1 issue within one hour. So far, I think that my rate is 92% or so. In this case, I found the SELECT N+1 issue on the very first page load.
Matching this to the code, we have:
Which leads to:
And here we can already tell that there is a problem, we aren’t accessing the authors. This actually happens here:
So we have the view that is generating 10 out of 12 queries. And the more results per page you have, the more this costs.
But this is easily fixed once you know what you are looking at. Let us look at something else, the actual root query, it looks like this:
Yes, I too needed a minute to recover from this. We have:
- One JOIN
- Two correlated sub queries
Jon was able to optimize his code by 660ms to 80ms, which is pretty awesome. But that is all by making modifications to the access pattern in the database.
Given what I do for a living, I’m more interested in what it does inside the database, and here is what the query plan tells us:
There are only a few tens of thousands of records and the query is basically a bunch of index seeks and nested loop joins. But note that the way the query is structured forces the database to evaluate all possible results, then filter just the top few. That means that you have to wait until the entire result set has been processed, and as the size of your data grows, so will the cost of this query.
I don’t think that there is much that can be done here, given the relational nature of the data access ( no worries, I’m intending to write another post in this series, you guess what I’m going to write there, right? ).