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? ).
More posts in "re" series:
- (12 Dec 2018) Going to async I/O
- (10 Dec 2018) Do we need a security review?
- (07 Dec 2018) Implementing parsing
- (04 Dec 2018) Multi platform and Valgrind
- (03 Dec 2018) Choosing the next direction
- (30 Nov 2018) Giving good SSL errors to your client…
- (29 Nov 2018) Starting with an API
- (27 Nov 2018) Error handling is HARD, error REPORTING is much harder