reEntity Framework Core performance tuning–part I

time to read 3 min | 501 words

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.

image

Matching this to the code, we have:

image

Which leads to:

image

And here we can already tell that there is a problem, we aren’t accessing the authors. This actually happens here:

image

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:

image

Yes, I too needed a minute to recover from this. We have:

  1. One JOIN
  2. 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:

image

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?Smile ).

More posts in "re" series:

  1. (19 Jun 2024) Building a Database Engine in C# & .NET
  2. (05 Mar 2024) Technology & Friends - Oren Eini on the Corax Search Engine
  3. (15 Jan 2024) S06E09 - From Code Generation to Revolutionary RavenDB
  4. (02 Jan 2024) .NET Rocks Data Sharding with Oren Eini
  5. (01 Jan 2024) .NET Core podcast on RavenDB, performance and .NET
  6. (28 Aug 2023) RavenDB and High Performance with Oren Eini
  7. (17 Feb 2023) RavenDB Usage Patterns
  8. (12 Dec 2022) Software architecture with Oren Eini
  9. (17 Nov 2022) RavenDB in a Distributed Cloud Environment
  10. (25 Jul 2022) Build your own database at Cloud Lunch & Learn
  11. (15 Jul 2022) Non relational data modeling & Database engine internals
  12. (11 Apr 2022) Clean Architecture with RavenDB
  13. (14 Mar 2022) Database Security in a Hostile World
  14. (02 Mar 2022) RavenDB–a really boring database