Episode 013 - The Tech Vault - EF Core Cartesian Explosions: Fixing a 13-Second Query

[00:00:01]Welcome back to another episode of The Tech Vault with Mitchell Sellers. Today I'm here to talk about Entity Framework Core Performance. I'm specifically looking at the Cartesian Explosion scenario and talking about how we can make sure that we don't get victim to one of these common pitfalls that we get within Entity Framework if we're not writing our queries correctly.

[00:00:22]So let's dive right in.

[00:00:24]As we look at best practices and the way that struct things are done, right? Sometimes we get applications where we see performance is maybe not quite what we expect. And this conversation really stemmed out of a real-life scenario that we had and we had a very interesting scenario. We had a web page that was taking 13-14 seconds to complete. It was putting a little bit of weird load on the web server as well as the database.

[00:00:57]But what was very interesting is as we had a couple of different pieces that came together. When we started to look at login, we had a few key pieces that came together to kind of paint the entire picture. The entire HTTP request took almost 14 seconds.

[00:01:13]Entity Framework put out a login entry that says compiling a query which loads related collections for more than one collection navigation blah blah blah blah blah.

[00:01:23]And then that SQL command itself took 4.118 seconds to execute. Now this is where things get a little bit interesting. The SQL query only took 4 seconds. Not great, but not mind-numbing performance impacts, right?

[00:01:38]However, the HTTP request took almost 14 seconds, which is completely unrelated.

[00:01:46]Now what's really unusual in this scenario is the query in question, if you viewed one piece of information, it would perform totally fine. If you viewed a different piece of information, it was incredibly slow. And then if you viewed one record, which happened to be the one that caused all of the problems in this scenario, the entire application became unresponsive. And so this is where things started to dive into how or what is a Cartesian explosion.

[00:02:18]We talk a lot of thing times about a Cartesian explosion being a scenario where we bring in more data than what we need, and that results in, you know, more data being processed. And so the reality here is that the sibling collections multiply the rows and they don't add to them. So if we have a parent item that has 10 posts and 12 contributors, we get 120 rows.

[00:02:43]As we go and continue on and make a more and more complex situation, it's very easy to get this completely out of control. So for example, if we then added something else that had eight individual items in there, right? We go from 120 rows to 960. So the SQL Server can respond quickly with it, thus our 4 seconds, but what comes back is a larger and larger and larger data set that then Entity Framework does processing on and tries to make things better for us along the way.

[00:03:18]So what does this really look like and how does this work? Like where might we see those performance penalties? And this is where things become very interesting, right? We know that the rows multiply.

[00:03:31]We're just talking about that.

[00:03:32]Obviously, we also have data duplication because we're bringing back rows that have a lot of extra pieces. Because of that, Entity Framework has to dedupe the results that come back, rebuild the graph and and overall data because we didn't ask for multiple records, right? We wanted more simple result set.

[00:03:54]And because of all of that, right? This results in buffering, allocation pressure, memory usage, and otherwise.

[00:04:01]So, we can see things that might be slow endpoints.

[00:04:07]High database CPU, timeouts, memory spikes within the application, and we may see inconsistent behavior along the way, right? Whereas one individual application might be okay, one user might be okay, one data record might be okay.

[00:04:26]So, as we dive in to what we actually saw in our logs, um it's very, very interesting to see what we have going on here. So, this is a structured logging scenario showing all entity framework perform uh query performance for particular application.

[00:04:46]We see here that we have a large number of queries that are executing. We have a HTTP get that responded in 13.7 seconds, and then we had a bunch of individual queries, but then we've got this one big query right here, and then immediately before it we have this compiling a query um you know, with no query splitting behavior comment in it. So, this is entity framework kind of warning us, "Hey, something silly might be happening." Notice we have this similar warning down here, but we have no queries that had a really bad performance. So, the idea here is that we're looking for any type of things where we're loading related collections with more than one navigation. And this is one of the things that's really important. When you look at the documentation, when you look at the guidance, and everything that everyone puts out there, they emphasize on the impacts with the use of include. But remember, a projection has the same behavior. And that's exactly what we ran into in this particular scenario.

[00:05:48]As we went to look at the query and the information that was contained within the query, it wasn't the use of an include. We have as no tracking, you know, everything here was the way that we wanted it to be, but the reality is is that we didn't get to a situation where we got a result that we expected. So, we grabbed from this table for a particular ID, but then we have these related tables, and this is where it's got out of control.

[00:06:27]Each of these calls to attributes where attributes where attributes where, these are the Cartesian explosions. So, instead of the query returning as we would expect this, right? A sub select or something similar, right? To be able to pull that back in one query, that's what we expect as we wrote this. The reality is is it resulted in each one of these entries duplicating all of the data rows. Each one of these duplicating all of the data rows. Each one of these duplicating all the data rows. As you can see how that might come together.

[00:07:05]The end of the day, we were able to completely change the behavior of this query by adding the single query hint as split query.

[00:07:16]What this did is told any framework, please don't try to do this all in one query. We want you or it's okay for you to make multiple database queries to do so. So, then it's going to take each of these individual wheres and make those individual SQL statements, and then it assembles the data back for us.

[00:07:36]Rather than getting a cross product join result or a Cartesian explosion that resulted in things becoming very dynamic and complicated. For this particular example, individuals that had five or 10 attributes were totally fine.

[00:07:55]An individual with 50 attributes is the one that actually broke the system. And so, the difference here in exactly how many records or how much data it takes to cause these problems is not as much as you would expect, right? We're not talking about millions of records, but because of the way that the multiplicative uh impact of it happens, it's very easy for things to get out of control quickly.

[00:08:23]After we made the change, what what happened? What what did our query look like? Well, as you can see here from the results, we now have a number of additional database calls that are are happening for the individual lookups, but everything is executing incredibly fast along the way. And the overall response time here is 664 milliseconds.

[00:08:49]This is running off of a low-powered environment over the public internet, so it'd be even faster in in real-world um production scenarios. And so, as we go and look at these results, we can see really quickly a single line change, we now no longer have the warning from Entity Framework that says, "Hey, you might be doing something bad." And our end users now are getting a very consistent behavior.

[00:09:15]What's most important here is as we go back and view one record that has five of those attributes, another record that has 50 of those attributes, the performance for each of those records to come back and be displayed are exactly the same. The memory footprint is the same. The CPU and other impacts are exactly the same.

[00:09:38]So, what does the split query do and why don't we just do it all of the time?

[00:09:43]Well, the reality here is you don't always want to do it, right?

[00:09:49]Entity Framework is really good about making your queries smart. But, it's also important that we take a look at scenarios that might result in us getting these kinds of things. So, for example, we want to use it when we have multiple sibling collections, especially like this example where you have the same collection that we're filtering over and over again.

[00:10:11]Or incredibly large result sets. Maybe we do a whole bunch of different queries all at once.

[00:10:18]We also might want to look at scenarios where the result counts or the metrics that we see from SQL look to be a little bit more complicated. For example, if we're working with something that shows you one record, but we have 5,000 records coming across from the database, that's a good time, right, for us to say, "Hey, this isn't going to be, you know, the case."

[00:10:42]What we're really looking for, too, is any additional network round trips.

[00:10:47]We we may get those with this, but it's acceptable in a lot of cases. It's not something you want to just blindly make the change, but you want to take a look at each of the individual things because sometimes it can get more complicated.

[00:11:02]More specifically, reference joins may repeat in each split query. So, you might end up doing a number of individual queries that join multiple tables together. If you are missing critical indexes in those individual queries, you might get yourself into a situation where the performance actually isn't that much better.

[00:11:24]So, the whole idea here is is that we want to look at how we're writing our query, how we limit our footprint, and then when and how we might want to give some of these query hints.

[00:11:36]If we grab only the columns that we need, we make our footprint narrower, right? Less things, even if there is a little bit of duplication, it's less of a a big deal. Pagination, another one of those scenarios. SQL aggregations, so, you know, doing things like sum and letting SQL handle it. All of these things help move that needle to where we don't have to get into the nitty-gritty on our split query behaviors, and we can kind of control it on our own.

[00:12:09]But, we should be looking at our log entries. We should be looking at our database communications, making sure that we understand some of the things that we want to do. And every single time that we see that profile um that warning, we want to take a look at it. The reality here is is that warning tells you, you can either have it as a split query or as a single query.

[00:12:37]If we see that warning as a profile and invitation, and we take a conscious effort at it, and if we decide that, you know what, we don't need this to be a split query, add the query hint as as single query. That will make that warning go away, and then you don't have to worry about it. Because by doing this, then we're able to be incredibly effective in what we're doing and how we monitor our applications.

[00:13:01]If we want to take a methodical approach, when we go look at this as we're talking about profiling, we want to start by the request looking at the request time and generated SQL. So, just like we did here, we knew how long the request took, we knew what the SQL was that was executing. We want to inspect what's going on. Are we bringing in things that we shouldn't? Do we bring more columns? Are we using a whole object when we actually only need a couple of properties.

[00:13:28]We also want to then look at the rows, the query execution duration, the number of reads, the memory impact not only on the SQL Server, but also on the web server for bringing things back.

[00:13:40]Then we take a look at can we modify anything? Can we split it out? Can we page? Can we aggregate? Are we missing any of those kinds of things? And then at the end of the day, we need to verify with whatever we have done by executing the same scenarios that we were working with in the past and making sure that we see a similar performance or a better performance improvement along the way.

[00:14:06]And anytime that we have a scenario where we have a projection, if there's a lot of variability or if there's a lot of possibility of there being a lot of data, it becomes very important to take these things into consideration.

[00:14:21]One example that I have of a from another project that is very common in the scenarios that we have. Um we will do everything that we can to make a um to to make an application project the data that we need. This query right here is a prime example of a wide query context where we're grabbing aircraft and we have a lot of information about the aircrafts. We have a large number of columns.

[00:14:55]But then we have a number of related collections information that also have a fairly wide bit of information. Notice there's a couple of joins and things here and some relations back to the parent object. But in here, we know, okay, we have one record here, but we may have five cost items, installed equipment, we may have another 10 or 20. We may have another 20 or 30 of these items. So, if we follow that same Cartesian explosion calculation and behavior, we know that we could be into a situation here where although we have one record that we're wanting to show, to get the data assembled back the way that we're looking for, it might take a lot more effort to be able to do it.

[00:15:45]Again, the simple as split query behavior here gives us consistency and performance, allowing us to project and write our queries the way that we want to, but ensure that we're not being overly eager. And again, in this scenario, we have no use of include or other scenarios that people typically initially trigger as saying, "Oh, well, this is why we might have a Cartesian explosion or some of these kinds of situations."

[00:16:15]Hope this has been helpful. Love to hear about your horror stories or other things with relation to Entity Framework or otherwise that we could talk about in the future episodes of The Tech Vault.