hello guys in this video I will show you how to debug and optimize a slow page or in fact slow SQL queries eloquent queries but from two angles usual what angle as they see in all the tutorials and all the videos is about the amount of SQL queries so n plus one problem it's called or eager loading and I will talk about that for those of you who don't know but the emphasis here is on the amount of queries and on slowness of one particular query but what is often overlooked is the amount of data being sent and the amount of RAM memory used on the back-end server and I will show you that in the exact example so demo project with list of hotels every Hotel belongs to a city and every Hotel has a lot of bookings in the database I've seated bookings twenty thousand rows cities hundred cities and five hundred hotels with random data and on the home page let's imagine we want to view hotel hotel belongs to a city and the amount of bookings for a particular week and if we load that page it's loading very slowly but not just because the amount of queries is big I've also installed laravel debug bar to debug the amount of queries and that debug bar is loading really slowly because of the amount of data and from the database point of view in eloquent relationships so hotel belongs to a city and Hotel has many bookings so the most straightforward way but wrong way to do that is in the controller just load all the hotels and in the blade do something like this so Hotel name city name and bookings count and then the page is loading really slowly so if we'll open our debug bar there are 1000 database queries performed overall time is 3 seconds which is not really terrible but if you have like hundreds of users on the page simultaneously you will have a problem so 1,000 queries because there is a query for all hotels and then whenever you call Hotel City name and it actually even shows that the bar bar in which view it is called it is firing the city then firing another city query and all of that and then whenever the bookings are called so bookings cow it is firing the booking for each of the hotel so basically one query for the hotel and then two queries for each of the hotel and if you have a thousand hotels there will be two thousand queries and not only that at the amount of models processed is pretty big so this is how many models are in the memory eloquent models in the memory for that script and that takes six to six megabytes of RAM so the first problem that we should fix is not to load the relationship in the blade instead we need to load them in the controller or whenever we load that relationship and correct way is hotel with and we load the city as the relationship and we load the bookings and I will show you with count in a minute so we'll load those in the relationships and let's see how it goes refresh the page three queries one 54 seconds so twice as fast and probably something is taken by web server my local or something like that but the queries are really really quick so milliseconds and milliseconds because what happens instead of one query per hotel there's one query for all the cities that are involved in the hotels and one query for all the bookings so great will fix the amount of queries right but it's still pretty slow 49 megabytes of RAM why because the modest amount didn't really change we're still dealing with 20,000 bookings and that still takes a lot of RAM and now look what happens if we see 20,000 more bookings I've prepared a cedar that seeds 20,000 more bookings it takes quite a lot of time but so okay great now we have 40,000 bookings and let's refresh the page and see what happens to remember 20,000 models and 49 max now we have 40,000 models and they all take memories so 81 Meg's of RAM and if we go even further like hundred thousand bookings or 200,000 bookings what you end up with is running out of memory so now I've seen 160,000 bookings i refresh the page and it's not even loading at all 500 error on my local web server or another error you may see is trying to allocate memory something and out of memory and that's because you're loading all the thousands of bookings in here although what you actually need is the amount of bookings here and this is where with count pays off so instead of loading all the bookings for City you do need the cities and they're not that many of them but bookings should be loaded in a different way so you're loading with city and then with count of bookings let's reformat that and in the blade it changes to bookings underscore count like this we save and let's refresh the page blazing fast right point for of a second and eighteen Meg's of RAM which is still quite a lot but still much lower and in fact we have only two queries not even three but two queries because it's combined we select all the hotels and the count of the bookings per Hotel we don't deal with all the bookings model with all the bookings data we don't take RAM we don't take memory for that it's all in one database query and in Hotel model insight so we're not saving bookings at all but the result is the same so to summarize my idea and thought is this query only the data that you will actually need if you need only the count of something query only the count so when optimizing the performance look not only at SQL data and the amount of queries and the individual queries but also how much memory is your script actually taking and how much data it is saved inside your PHP script and if you want more videos like that you can support this channel by doing one of two things and well in one of my courses at laravel daily teachable comm or use our laravel quick admin panel generator at quick admin panel com see you guys in other videos
