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:
- (03 Jan 2019) I’ll be in CodeMash is next week
- (09 Jan 2018) Early bird pricing for RavenDB workshops about to close
- (24 Dec 2013) End of year 32% discount coupon is still valid
- (24 Apr 2013) RavenDB Webinar Tomorrow
- (07 Oct 2011) RavenDB and NHibernate courses–New York coming up
- (24 Aug 2011) Advanced NHibernate Course–Warsaw, October 2011
- (26 Jul 2011) RavenDB & NHibernate Training - August 15 - 16, Chattanooga, TN
- (12 Jan 2011) NHibernate Course in Dallas, March 2011
- (11 Feb 2010) Linq to SQL Profiler goes 1.0 on the 14th