Faster Eloquent: Avoid Accessors with Foreach

hello guys today we will optimize a laravel project small laravel project with huge performance problems and it is a pretty typical scenario in eloquent when people do grouping in php instead of database so here's the scenario you need to count the users by some kind of parameter and group them but that grouping isn't as simple as just one column in the database like group by so what do you do then you get all the users of course selecting only by id for performance then you have a separate service doing for each of the users grouping them into separate variables and returning them and then as a result you have something like this on the screen so grouped users by numbers which is cool but the problem is that with 30 000 users and 10 000 relationship records this page took 40 seconds to load what is the reason and how to fix it watch the video until the end so the main problem is not actually loading all the users it's a lot of data 30 000 rows but still the problem is that by doing for each of those users this is not a field in the database identity is an attribute in the user model which is get identity attribute and then inside it has some logic which is quite a typical scenario if you think about it whether user is fully identified or have identified it is based on a real project by the way so i'm not making this up and the logic of those is full identified is based on the fields from the user like name and phone and based on whether there are related records like networks which is has many have identified is also networks is empty or not and is ghost also checks the relationship for networks and ghosts there are two separate tables so in the user model there's networks and ghost has many relationship and the problem is the amount of queries so here's how it works you get all the users then for each of those users you call the attribute so 30 000 calls to this function then for each of those records it's calling the relationships which are not eager loaded from the controller so under the hood there's huge amount of queries to the database although it seems that the primary query is this one there are actually many more queries under the hood with this and how can we optimize that to measure the performance i will use a tool called spitey laravel ray it's not free i will link that in the description below if you want to purchase that you can use something like laravel debug bar but i just like how ray visualized the breakpoints so in the controller i can do measure label first call and then measure label with another label and it will show how much time passed and what was the memory usage so this was the result now so 33 seconds it's not 40 but 33 which is still a huge thing and 100 megabytes of ram you're kidding me it's quite a lot interesting thing in the database we have 30 000 users fake data and 10 000 networks which is not a big amount of columns right but here's an interesting thing let's delete all the networks truncate the table okay and delete all the ghosts so there are no related records then there are only users so it should only have 30 000 rows and not really pollute the memory and use too much ram right let's refresh and see what happens so this is the result those numbers are zero because we don't have any relationships anymore but the time didn't drop it's still 34 seconds so the amount of queries is still huge although there are no actually records in the database the memory dropped from 100 megabytes to 30 megabytes of ram used but still the amount of time is totally unacceptable let's optimize it first of course you would think that we need to do eager loading in the controller so with those relationships like networks and ghosts right so if we do only that let's refresh and see the result okay here's the result and by the way i've receded those 10 000 records so it's much better 5.8 seconds but still 38 megabytes of ram is quite a lot actually and even 5.8 is still unacceptable what are other options probably a lot of you notice that we don't actually use the networks and ghosts themselves here we use only the count in the user model here we have is empty is not empty so we don't actually care about the network's data we care only about the amount so instead of width let's do with count like this and then in the user model instead of doing is not empty we do networks underscore count is bigger than zero like this and is empty becomes networks count equals to zero and same with ghosts ghost count is not empty meaning bigger than zero and networks is empty is equals to zero and also one more thing we need to change in the controller select id then is not enough because it needs to select networks count and ghost count now let's refresh and see what we have we refresh and see the result milliseconds so it's less than a second and not only that the results may be incorrect even from that service so if you use some kind of fields in your user model like this name or this phone but don't load it from the controller remember we had select id and that was from the original source code not by myself those fields may be not accessible in the user model and you may not even realize it so we optimize the performance and probably the accuracy of the query but actually i still don't like the approach of using with count here networks count because that attribute may be called not from this controller it could be called from another controller or from some kind of service or other file so to rely on with count being called is not really a good practice what i would advise is the avoid of that circle loop of controller then for each and that for each relies on attributes that is a potential recipe for disaster both in the performance as you can see and also in unpredictable results of fields at the time of that for each loop what is loaded what has value or not you won't necessarily see the error from eloquent or laravel maybe that would transform that isn't all this name yeah it is null okay so let's move on but that's not necessarily what you expected so what i would advise is to have a separate calculator in the service or some kind of file you may do that in the controller as well but still if you need to calculate the users by group do that in one file where you would load what you need how you need without any for each loops and in fact instead of loading all the controllers and then doing for each why don't we have separate four queries for each of that it may sound weird why would you do four queries instead of doing some kind of grouping but it's pretty hard to do group by if the condition of that grouping isn't a linear database field so let's measure the performance of doing user count this and count that so i've prepared that code for queries to the database it sounds like a lot maybe so user aware something user where user has or doesn't have the relationship so we don't actually even check the count it's has or doesn't have which is almost the same as these conditions just in eloquent language so four queries what is the result here's the result 757 milliseconds let's refresh just to make sure 659 so it's roughly similar like you would do with count but now you have a totally separate calculator only for the things you need and you don't really risk the incorrect data between controllers and between models and attributes and also you download all the 30 000 users here because you don't need them so you may even do this this remove all of those in service you don't need the users anymore and now let's refresh refresh the page now it's 196. so i forgot to remove that it's 0.2 of a second so when you don't even load the users in the first place from the database that is probably the most efficient and of course on top of that you can introduce caching or do some other optimization of calculations of those but that's outside of this video so in this video we change the query from 30 seconds plus 2.2 of a second so the overall conclusion and what i wanted to demonstrate in this video is try to avoid the approach of getting the data for each and then attributes from the model if you need the calculations make the calculations make the database work and not for each loops in php i hope it was helpful and if you want more tips on eloquent i remember i had written on laravel news a guest post back in 2018 but most of that is still relevant so if you want to find out more tips on how to do something more efficient or interesting and eloquent read the article i will link that in the description below and see you guys in other videos

Channel: Laravel Daily

Views: 23,145

Keywords:

Id: 12KCl82L48Y

Length: 9min 35sec (575 seconds)

Published: Sat Sep 25 2021