Slow Excel report with MySQL queries inside nested foreach loops [Laravel 5.3]

Question

I'm facing a problem with slow times creating XLS/XLSX reports using Maatwebsite plugin for Laravel (around 5 - 30, 30 - 60 mins depending on the conditions of the report) which is a lot of time!!.

Searching through the web I encountered that Maatwebsite uses PHPExcel, which is slow and memory consuming if there's a lot or registers in the generation of the XLS/XLSX file.

Checking times, I have encountered that my queries grow almost exponentially because of nested foreach loops, the basic generation of the report is this:

$clients = Client::all(); // can change depending on conditions, worse case with all clients
Excel::create('Client Statement', function($excel) use ($clients) {
     $excel->sheet('Sheet 1', function($sheet) {
          $row = 1; // row number
          foreach($clients as $client) {
               $sheet->row($row++, [$client->data1,...,$client->dataN]);

               $charges = Charge::get_ByClient($client->id);

               foreach($charges as $charge) {
                    $sheet->row($row++, [$charge->data1,...,$charge->dataN]);

                    $payments = Payment::get_ByCharge($charge->id);

                    foreach($payments as $payment) {
                         $sheet->row($row++, [$payment->data1,...,$payment->dataN]);
                    }
               }
          }
     });
});

The basic explination is:

1. Get all clients
2. For every client, get all the charges
3. For every charge, get all the payments

The problem is, I have around 1500 clients, every client can have betwee 10 - 100 charges, and every charge can have between 1 - 5 payments which results in slow performance. To that, sum the time the library takes to generate the XLS/XLSX file.

Any suggestions? Thanks in advance.

Answer 1

Just to clarify this, I indeed used Eloquent's relationships as @Mark Baker suggested to speed up things, my problem was the typical N + 1 Problem. To resolve this issue I used Laravel's Eager Loading