DEV Community

loading...

Discussion on: Laravel: Performance Boost!

Collapse
simpledevme profile image
Simpledev

"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.

Collapse
jringeisen profile image
Jonathon Ringeisen Author

So how would you improve the Eloquent query to obtain the same result? I’m open to suggestions.

Collapse
simpledevme profile image
Simpledev

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.

Thread Thread
jringeisen profile image
Jonathon Ringeisen Author

@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!

return Income::selectRaw("substr(date_income_received, 1, 4) as year")
            ->whereUserId($request->user()->id)
            ->groupBy('year')
            ->pluck('year');
Enter fullscreen mode Exit fullscreen mode
Forem Open with the Forem app