I recently started using phpdebugbar start_measure(name, label) and stop_measure(name) to see how I can make performance improvements to my platform.
If you didn't know, you can use phpdebugbar to measure how long a certain function takes to execute. To find this out you simply add start_measure(name, label) at the beginning of your function and stop_measure(name) at the end of your function and when you run that snippet of code in your browser you will get a result in the timeline tab, like below:
As you can see from the image above, this snippet of code is taking over 2 seconds to execute and it's using 203MB of memory 😳. This can be improved, a lot!
Using Eloquent
Here is the snippet of code that is causing the issue:
$results = $request->user()->incomes()->get();
$years = $results->unique(function($item){
return $item['date_income_received']->year;
})->map(function($item){
return $item['date_income_received']->year;
})->sort()->toArray();
return $years;
The goal of this code is to get a collection of incomes in the database and iterate through them to get the unique years for the incomes. So for instance, if I have 100 incomes in the database and the incomes are for both 2020 and 2021, the results will be an array with ['2020', '2021']. This way I can display the years to the user and they can filter through the incomes based on those years.
What's the issue?
Well, the issue is with $results = $request->user()->incomes()->get();
The SQL statement being generated is as follow:
select * from `incomes` where `incomes`.`user_id` = 1 and `incomes`.`user_id` is not null
You can see in the image above that this query is getting over 70,000 models and a lot of columns that we don't need. So, how do we improve this query. By doing this:
return Income::selectRaw("substr(date_income_received, 1, 4) as year")
->whereUserId($request->user()->id)
->groupBy('year')
->pluck('year');
The SQL statement for this is as follow:
select substr(date_income_received, 1, 4) as year from `incomes` where `user_id` = 1 group by `year`
Note: I originally had ->select([DB::raw('YEAR(date_income_received) as year')]) in the query but this made my tests fail due to YEAR() being incompatible with SQLite. After trial and error, I found substr() to be a good alternative. Although I think I'm planning to revert back to using MySQL for my tests, and in turn, I'll switch it back to YEAR()
This snippet of code takes 338ms and only uses 6MB of memory 🤯. That's a massive improvement and we're getting the same result.
Initial Query:
Over 2 seconds, 203MB of memory.
Improved Query:
338ms and only 6MB of memory.
Conclusion
When writing queries it's always a good idea to have a way to see the performance of your query so that you can ensure that your writing optimal queries. It's easy to do something as simple as $request->user()->incomes()->get(), but as you can see from above this is not an optimized query and we can do better.
Top comments (4)
"Using Eloquent in this situation is clearly a bad idea as it causes severe performance issues."
It's this Eloquent request that is causing the performance issue. Not Eloquent.
This Eloquent query and this Query Builder query do not produce the same SQL query, and I think there is a N+1 issue.
You can achieve the same performance result with Eloquent if the query is well implemented.
So how would you improve the Eloquent query to obtain the same result? I’m open to suggestions.
This request $request->user()->incomes()->get();
produce something like that:
SELECT * FROM users WHERE id = 2
SELECT * FROM incomes WHERE user_id = 2
You don't need all the columns from each table, this query consume a lot and look at the number of models in the php debug bar, it's crazy !
I didn't test because I don't have the data and time but maybe try something like :
Income::select(DB::raw('SUBSTRING(date_income_received, 1, 4) as year'))
->whereUserId(Auth::id())
->groupBy('year')
->pluck('year');
This way you request only the date_income_received column in one table like in your query builder tip. (I used DB::raw('SUBSTRING()') because you wrote having an issue with SQLITE)
If you want the user's data in the result, you should use eager loading like here laravel.com/docs/8.x/eloquent-rela... and select only the column(s) you need. This way avoid N+1 issues.
You can find good tips that improve your Eloquent perf here reinink.ca/articles.
Hope this helps.
@simpledevme you are correct! The following Eloquent query has the same improved performance. I'll edit the article this evening. Thank you for that information!