A silent killer lurks within your application, and well, eventually it'll bite. So this is the scenario. My admin team has been complaining that the products page inside of the admin side of the application is, it's not to the point that it is just incredibly slow, but there is a noticeable slowdown because whenever we launch the application, you know, these order pages just loaded in pretty snappy. That was pretty snappy too.

these order pages just loaded in pretty snappy. That was pretty snappy too. But you know, as our data has grown, there's been this noticeable delay in displaying this information. And of course this is paged. So, you know, the, the performance issue that we are going to see is going to be somewhat mitigated because of the paging, but as our data grows, things are going to go wrong.

because of the paging, but as our data grows, things are going to go wrong. And if you saw at the top of this, it's the n plus one query problem. Th this is something that I have talked about a lot, and I guess it's something that needs to be talked about a lot because even if you know what this problem is, even if you know how to mitigate it, sometimes it creeps into your production environment

even if you know how to mitigate it, sometimes it creeps into your production environment because, you know, maybe you've stubbed something out and then you think, oh, I'll, I'll go back to that later. And you don't, I'm not speaking from experience, but you know, that could happen. And then whenever you push that out into production, then you have this problem. And unfortunately, it's one of those issues that

then you have this problem. And unfortunately, it's one of those issues that you don't really notice because interacting with the database, unless if you're dealing with this huge amount of data, is relatively snappy until it's not whenever you start dealing with a huge set of data. So, you know, something like this is difficult to find to begin with. So what I recommend you doing is, you know, use tools

to find to begin with. So what I recommend you doing is, you know, use tools and, you know, Laravel has the telescope, which is a fantastic little, um, um, tool that you can use to look at the different parts of your application, see what database queries are going on behind the scenes. There's also this cool little thing, it's called the Laravel Debug Bar. Now, this isn't built into Laravel, it'd be really cool if it was.

Now, this isn't built into Laravel, it'd be really cool if it was. But this gives us an insight about various parts of not just, you know, the database access as far as the queries are concerned, but we can also see the request information if there's any messages. We can also view the timeline of everything for handling this particular request, the views involved, the queries and the models.

handling this particular request, the views involved, the queries and the models. So we're gonna focus on the queries because we can see that there's 192 queries just for displaying this page. And it, it is this page, it's every page that we look at whenever we are viewing the orders. And since this is all Page United, you know it's going to be the same. We are, is that the same, what was it before?

you know it's going to be the same. We are, is that the same, what was it before? I don't remember. It was 1 97 here. So if we, uh, go to page 10, 1 91. So there's gonna be some variance in the queries that we see, but before we look at the solution for this, let's take a look at the code. So here we have the index view for the order controller, for the admin side, and we can see, uh, right here, this is our query.

for the admin side, and we can see, uh, right here, this is our query. We are simply fetching the latest based upon the time that the order was placed. And then we are paginating those orders with 50 to a page and those orders get passed onto the view, which if we take a look at the view, it's, you know, there's nothing outta the ordinary here. All we are doing is iterating over the orders. So here we can see the order table.

All we are doing is iterating over the orders. So here we can see the order table. Here we are iterating over the orders and what do we do? We show the order information, we have the order number, we have the user that placed the order, but not just the user. We're getting the name of that user, the email of the user. We also get the items that is part of that order. We get the count of the items. So there's a lot of extra information that we are showing

We get the count of the items. So there's a lot of extra information that we are showing because it makes sense to show these things. This is the admin side. We want to display as much order information that we can for the admins to view. The problem here though is that there's silently this, this query issue to where we are issuing multiple queries for each individual order. So we've already fetched all of the orders

multiple queries for each individual order. So we've already fetched all of the orders that was the first query. Now we are iterating over those orders and as we show the order information, we also want to show some of the related information for those orders, such as the person that placed the order. So that's gonna cause another database query because we have to pull in that user information so that we can show the name and the email

because we have to pull in that user information so that we can show the name and the email and then it comes to the items. We want to show the product information about the items that were part of the order. So that's a whole nother set of queries because we have to go out and fetch those. And we are doing this every time we go through this loop for every order. We are fetching the user, we are fetching the items just so

through this loop for every order. We are fetching the user, we are fetching the items just so that we can show all of this related information. It doesn't look like we have caused a problem, but we have, it's, it's a silent killer because as our data grows, it's gonna kill the performance of our application. So what do we do? Well, the thing to do is to eager load the information that we want to use. And there's a really easy way that we can do this

to eager load the information that we want to use. And there's a really easy way that we can do this and I would love to just say do this, but let's at least look at what we might have used to have to do to fix this kind of problem. Because remember, you know, we have 191 queries. So we have one que to fetch all of the orders. Then we have a ton of other queries to fetch the user information, the products

Then we have a ton of other queries to fetch the user information, the products and the items within that order, things like that. We want to get this down as much as possible. So the first thing that we can do then is say that, okay, we want to work with our orders, but we also want to work with, you know, certain related information such as the items that are part of the order as well as the user that's placed the order. So by taking this approach,

of the order as well as the user that's placed the order. So by taking this approach, what we have essentially done is eager loaded the items in the user. Now this doesn't necessarily mean that we have gone ahead and fetched all of that information. What we've done is told eloquent to prepare that we are going to access this information, do so as efficiently as possible, and we can see a dramatic change here just

as efficiently as possible, and we can see a dramatic change here just by going back we can refresh and we see that we, well we cut basically our queries in half. That's really great, but we could do more because you know, we still are displaying 50 products here or or 50 orders. We should still be knocking this down quite a bit. But if we take a look at, you know, the list of queries

We should still be knocking this down quite a bit. But if we take a look at, you know, the list of queries that we have, we can see what we've gone down. Now remember we went from iterating over just our orders and then going and fetching the user information, the item information and the product information, all that stuff. Now we can see that we are fetching our orders here, but then we want to work with the items for that order. So we are going

but then we want to work with the items for that order. So we are going to select everything from the order items table where the order ID matches the orders that we are currently working with. So eloquent behind the scenes has now started to take the orders that we are working with and it's smart enough to know what those orders are. So it can start to select the information that we have requested, such as the user information

So it can start to select the information that we have requested, such as the user information and the items that were related to those orders. Here we can also see, you know, the users the, the same way we're saying, select everything from the users where the user ID is in and then we can see these user IDs. Now we know what these user IDs are because it's coming from the order table. We know what users have placed that order because there's a foreign key inside of that.

We know what users have placed that order because there's a foreign key inside of that. In fact, we could take a look at that if we wanted to. If we look at the create orders table, we have the ID for the order, but then we have the foreign ID for the user that placed the order. So again, eloquent knows about these relationships, it knows how to essentially prime itself to load the related information for an individual order in this particular case.

to load the related information for an individual order in this particular case. But you know, we want to get this as efficient as possible. And currently we have 93 queries to display, 50 orders. We need to get that down even more. Now if we take a look at our view, once again, you know, we are iterating over the orders and then here we access the user

we are iterating over the orders and then here we access the user to get the name and the email. That's all information, uh, that is provided with the user. So there's, there's no nested relationship there, but if we take a look at the items, well what do we have? The item has a product, which is another relationship. So we, we still have some kind of issue here because as we have eagerly loaded the items, we are now going out

because as we have eagerly loaded the items, we are now going out and fetching the product information related to those items. So not only can we eagerly load just, you know, directly related information, we can also load nested related information. So for our items, we want the product information that goes with those items. So if we go back to the browser and we can refresh, we've now locked that locked,

So if we go back to the browser and we can refresh, we've now locked that locked, we've now knocked that down from 93 queries to six. And this is to display 50 orders all within, you know, a given page. So we haven't really done anything except made our database queries much more efficient, which means now it doesn't really matter how much our data grows, we can still access that information as efficiently as possible

how much our data grows, we can still access that information as efficiently as possible because we are eager loading that information. So we have made our application much more performance. We've gone from 190 something queries to what six we're done, right? Some would say yes, some would say no. And the people that would say no have usually a very good reason because in a production environment, in my experience,

usually a very good reason because in a production environment, in my experience, a production environment has our web application running on one machine and our database is on a completely different machine. So making queries and getting the results from that query is part of the network. It takes time for that data to transfer between those machines.

It takes time for that data to transfer between those machines. In which case, what are we doing here? We are selecting all from our orders, we are selecting all from the order items, we are selecting all from the products. Are we using all of that information? No. I mean we can go through and we can pick out each individual, uh, attributes that we would want to display and we can do that.

individual, uh, attributes that we would want to display and we can do that. I already know what we need. So let's take that approach. Uh, I'm gonna reorder these so that we will get the user first, then we will get everything else. So we want just certain columns from the user's table, which would be the id, the name and the email because we don't need anything else whenever we display that information inside of our view. But we also need to display the items in which case

whenever we display that information inside of our view. But we also need to display the items in which case we need the ID of the item. We also need to know the order ID as well as the product id. Now, we didn't go over, you know, the, the migrations and things like that for these, but just know that the items table has foreign IDs for the order and it has foreign IDs for the product. So it makes sense to go ahead and load those

for the order and it has foreign IDs for the product. So it makes sense to go ahead and load those because we're gonna need them in order to match the items with the orders and the products, in which case we have items product and we want the ID and we want the name because we want to use that information inside of our views. So if we go back, we're not gonna see any change as far as the number of queries, but we will see a change in our queries themselves

as the number of queries, but we will see a change in our queries themselves because here we can see that we are selecting the id, the name and the email from users. We are selecting the id, the order ID and the product ID from order items. And whenever we get to the products, we are selecting the name and the id. So we have effectively shrunk the data that's coming back from the database so

So we have effectively shrunk the data that's coming back from the database so that if it is on another machine, our network communication between our server or our web server and the database server is much more concise, which is great. I mean we can't complain about that. We, we, we've gone through this process of eagerly loading and this is what we've had to do in the past and we can still do this.

and this is what we've had to do in the past and we can still do this. I mean this obviously works, but we now have a tool that we can just set it globally and we can say, Hey, just eager load all of our data and we're gonna be fine. So let's do that and then we'll take a look at the results. So we're gonna change this back so that we are just going to order the latest page in eight 50

So we're gonna change this back so that we are just going to order the latest page in eight 50 and if we take a look, you know, our queries are back up to 191, but what we're gonna do is go to our app service provider because here we are essentially going to say, Hey, eager load everything. And we're gonna do that with thematically eager load relationships. And I misspelled eager, so this is just gonna do it for us.

load relationships. And I misspelled eager, so this is just gonna do it for us. We do need to pull in a use statement for the model class, but that's all we have to do. We can go back, we can refresh, we are back to six queries. Now our queries are still selecting everything from those tables. Do we do that? Do we not? That's that's for another time. But you know, to start things off, if you're one of those people that you're like me,

But you know, to start things off, if you're one of those people that you're like me, you prototype something, you forget about it and then push it out. This is a great little safety blanket. There's other safety blankets, but this one just makes everything work. It's fantastic. It's, I use it all the time. So what do we learn? Well, we learn that we really need to eagerly load our data.

So what do we learn? Well, we learn that we really need to eagerly load our data. That's really just what it comes down to because we went from an application that issued 190 something queries to six and we end up with the same results with a lot less queries and a lot less time. And it doesn't matter if our data grows, we are still going to see those six queries. So it's great, it's fantastic.

to see those six queries. So it's great, it's fantastic. Eagerly load your data and it really doesn't matter how you do it. You can do it with the width method that we talked about or you can just set it globally. The key thing to remember is to just eagerly load and you won't have a silent killer lurking within your application.

application.