Retrieve collection with calculation takes too long to load (Laravel)

Question

I'm working on a collection that needs to calculate some data for each row and it takes too much time to load into view. The problem is I defined an accessor and inside that accessor will perform some calculation and if the data is too big or when user retrieve too many row at once.

Example Model:

public function getCalculationAttribute()
{

    $score_ids = Score::whereIn('id', $this->scores->pluck('score_id'))->pluck('id');
    $count_score = $count_score->count();
    $penalties = Penalty::whereIn('score_id', $score_ids->toArray())->count();
    $balance = $count_score - $penalties;
    $another_score =  $count_score > 0 ? ($balance / $count_score) * 0.7 : 0;

    return [
        'field_a' => $count_score,
        'field_b' => $penalties,
        'field_c' => $balance,
        'field_d' => $another_score
    ];
}

Example Controller

public function index(){
    $data = ExampleModel::get();
    return view('example', ['data' => $data]);
}

Example blade

@foreach($data as $row)
    <p>{{ $row->calculation['field_a']}}</p>
    <p>{{ $row->calculation['field_b']}}</p>
    <p>{{ $row->calculation['field_c']}}</p>
    <p>{{ $row->calculation['field_d']}}</p>
@endforeach

When I didn't need the calculation attribute it works perfectly fine, but when I do and I know each of them will be running query and calculation and it will take forever. Is there any good practice on retrieving data with calculation or any suggestion I can modify this to improve the performance? The code above is just an example. Thank you in advance!

What have you tried to check these issues? Are the fields indexed at database level? — Nico Haase, Mar 09 '22 at 14:27
https://stackoverflow.com/questions/59981047/how-to-execute-an-explain-select-on-a-laravel-builder could help to inspect the execution plan — Nico Haase, Mar 09 '22 at 14:28
@NicoHaase Yes I've checked all field that should've indexed are indexed but I think maybe data was too big and each individual record need to run the query and calculation that's why it takes too long. I was wondering should I create a log table and run a CRON to save daily calculation or any other better way — Grvx, Mar 09 '22 at 14:36
This seems like a lot of really redundant querying : $score_ids = Score::whereIn('id', $this->scores->pluck('score_id'))->pluck('id'); you seem to have the score_ids from the inner pluck('score_id') — Tarek Adam, Mar 09 '22 at 14:51

score 0 · Accepted Answer · answered Mar 09 '22 at 14:55

You've got an N+1 query issue with this code. Each time you loop $data and call $row->calculation, you're executing 3 extra queries:

Score::whereIn(...);
$this->scores->pluck('score_id');
...
Penalty::whereIn(...);

You're calling $row->calculation 4 times... I'm pretty sure that means 12 additional queries per row in $data, since get{Whatever}Attribute() doesn't have any kind of caching/logic to know you've called it already.

If you save $row->calculations to a variable, you can reduce that a bit:

@foreach($data as $row)
  @php $calculations = $row->calculations; @endphp
  <p>{{ $calculations['field_a']}}</p>
  <p>{{ $calculations['field_b']}}</p>
  <p>{{ $calculations['field_c']}}</p>
  <p>{{ $calculations['field_d']}}</p>
@endforeach

Additionally, you can eager load the scores relationship to reduce it a bit more:

$data = ExampleModel::with('scores')->get();

Including that will make $this->scores->pluck('score_id'); use the pre-loaded data, and not call an additional query.

Lastly, try to use relationships for your Score::whereIn() and Penalty::whereIn() queries. I'm not sure how you would define them, but if you did, then including those in your ->with() clause will hopefully completely remove this N+1 query issue.

Thank you so much this improve my performance a lot by saving it into a variable! from a 8 to 10 seconds loading to only 2 to 3 seconds. — Grvx, Mar 09 '22 at 15:56
Haha yeah, with that change, you're moving from 12 additional queries per row to 2, which is a huge improvement. And again, this can be optimized further if you can get the result of those 2 nested queries into a relationship. Happy to help! — Tim Lewis, Mar 09 '22 at 15:58

Retrieve collection with calculation takes too long to load (Laravel)

1 Answers1