Laravel API Resources extremely slow

is your table indexed properly? how many records do you have currently?

also for an index() people generally use the paginate() instead of all() as it makes more sense.. and as you haev found is slow -

also all these issets() instead of that look into the default() relationship OR you can just do

optional($this->user)->fullname..

Hi Level18. I have no index in my table for now. I'm gonna try to see if it improves the performance. I have 870 records in my table. I plan to use paginate() but it won't solve the problem, merely hide it. Thanks for the default() option, exactly what I was looking for !

Ok so it seems that adding an index doesn't change much about the performance. Maybe it's expected to last 30 seconds and we have to use pagination ?

Hey level 1 - 870 records.. doesnt sound a lot - and pagination doesnt HIDE The problem it is the solution.- unless you are doing something unique - no one would take a look at all 870 records in your table - so you are wasting precious CPU time.

in any case if you could post some code (your controller & blade & db migration)

Hey sorry for the mistake shez1983 :). I am building a REST API so I don't have much more code than I provided. I just have the routes:

Route::apiResources([
    'employees' => 'EmployeeController'
]);

and my Employee model:

class Employee extends Model
{
    use SoftDeletes;
    protected $dates = ['deleted_at'];
    protected $guarded = [];
    public function businessUnit()
    {
        return $this->belongsTo('App\BusinessUnit')->withDefault();
    }
    public function company()
    {
        return $this->belongsTo('App\Company')->withDefault();
    }
    public function costCenter()
    {
        return $this->belongsTo('App\CostCenter')->withDefault();
    }
    public function getIsActiveAttribute($value)
    {
        return (bool) $value;
    }
    public function user()
    {
        return $this->belongsTo('App\User')->withDefault();
    }
}

Interestingly enough, caching the collection with file doesn't make it any faster:

    public function index()
    {
        return Cache::remember('employees', 24 * 60, function () {
            return EmployeeResource::collection(Employee::all());
        });
    }

The problem comes from the relationships. The processing of the resources is fast as long as there is no relationship involved. But adding relationships slows down the processing. But I have no idea how to make them faster...

Always use "Eager Loading" when you do loop over records and want to retrieve relationship values otherwise you create the famous N+1 problem.

In your current code for each employee you do 4 or 5 extra queries. So for 900 employees you do 900*5 = 4500 queries

https://laravel.com/docs/5.7/eloquent-relationships#eager-loading

Tip: add laravel debugbar to your project (https://github.com/barryvdh/laravel-debugbar ). You can easily see how many and which queries are being executed. This gives you a good insight if you are doing this correctly. If you see multiple times the same query you probably can optimize that with eager loading

If I get you correctly @wuanshu , you have the issue of N+1. You can do eager loading and use notation eager loading.

In addition, you may apply subquery if it takes a big amount of memory. A suggestion to take a look the following articles-

• http://laravel-school.com/posts/deep-diving-into-laravel-subquery-14
• https://reinink.ca/articles/dynamic-relationships-in-laravel-using-subqueries

If you don't eager load the relations then line such as this;

'manager' => isset($this->user->manager) ? $this->user->manager->fullName : null

will do a database query for every row in your user table (eg 870 times)

In addition, all that data has to be loaded and assembled in memory which gives you a big footprint for the application and might cause scalability problems.

Even though you are writing this as an API, I would still install Laravel debugbar and test your code in a simple browser call so that you can see the debugbar output. It will tell you how long each database query takes and how much memory.

PS

Everyone should watch this video from Jonathan Reinink at least once...

https://laracon.net/2018 --- scroll down to Jonathan Reinink

Thank you all for your precious help ! Indeed the issue of N+1 occured. The processing time dropped to 5 seconds now. Still seems like a lot but pagination should be enough now. @snapey I will watch that video for sure. @tisuchi Thanks for the links. I was looking for that kind of feature. @click I will try the debugbar. Although I'm curious as to the differences with telescope.