Best method to export very large data (160k rows collection)

[lordisp]

This is more a conceptually question to share ideas. Once I have the final solution, I'm more than happy to share it with the community. A research results that such huge exports are not very common.

I'm fetching data from an REST API, make a collection and let Laravel export store it on disk before sending the sheet via mail. This raised numerous concerns in terms of resources and performance. Of course using Laravels job queueing engine.

Conceptually I build a class for the API call that returns all (160k rows) data which runs as a job out of an event listener that calls the laravel export class

API Class (truncated)

class CredentialUserRegistration
{
    // relevant method for the colletion to export 

    public static function withFilters(array $filter, $all = false): Collection
    {
        $instance = new static();
        // code to fetch data from rest api
        return $instance->response;
    }
}

once the data are available, laravel export shall continue to make the export on disk

Laravel Excel Class (truncated)

class ExportCredentialUserRegistration implements FromCollection
{
    public function __construct(public array $filters){}

    public function collection()
    {
        return CredentialUserRegistration::withFilters($this->filters, true);
    }

}

The actual Job Class (truncated):

class ProcessCredentialUserRegistrationReport implements ShouldQueue, ShouldBeUnique
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    private string $uniqueId;

    public $event;

    public function __construct($event)
    {
        $this->uniqueId = Hash::make($event->user->id . json_encode($event->filters));
        $this->event = $event;
    }

    public int $timeout = 0;  // just for testing

    public function uniqueId(): string
    {
        return $this->uniqueId;
    }

    public function handle()
    {
        $path = $this->event->user->oauth_id . '/CredentialUserRegistrationReport.xlsx';

        $directory = storage_path('app/' . $this->event->user->oauth_id);

        $stored = Excel::store(new ExportCredentialUserRegistration($this->event->filters), $path);

        if ($stored) {

            Mail::to($this->event->user)
                ->send(new SendCredentialUserRegistrationReport($this->event->user, $path));

            File::deleteDirectory($directory);

        }

    }

}

The ProcessCredentialUserRegistrationReport::class will be called by and event over a dedicated connection for long jobs with an 3h timeout setting: 'retry_after' => 10800

If I filter the results down to few hundred rows it works fine but coming the 100k> it looks like the sheet is losing few thousands rows.

Eg: requesting 132k rows, getting 59k in the sheet.

I could (and maybe will) debug this behavior deeper but I'm very concerned about the strategy dealing with such missive data. Temporarily I extended the memory in the infrastructure chain (php_ini, queue container, etc...) to 3Gb which should be fine from a monitoring perspective but I believe the entire concept is miles away from ideal.

So I decided to open this discussion for sharing ideas how to deal with 100k+ using Laravel Export.

What do you think about this and what would you do differently?

Many Thanks!

[patrickbrouwers]

How long does it take to fetch all data from that API? And how much memory does that consume? It might be interesting to try and load it into a lazy collection, that will already bring down the memory usage.

[lordisp]

156.951k rows took 5min 33sec with 216.01M usage on my dev machine. Just the collecting the data from the API without any laravel-excel voodoo. lacy collection is an interesting idea. I'll try this.

[lordisp]

it took me a while to learn about lacy collection and do the refactor. The new results are better in terms of resources but the performance is now worse. However the results look more stable. I have not tested it over the entire suite with the actual export. The approach looks very promising, so I'll continue from here and keep you posted.

memory "36.79M"
rows(156943)
time(8m 32sec)

@patrickbrouwers would you see any enhancements?

class CredentialUserRegistration
{
    use WithRestFiltering;

    public function __construct()
    {
        $this->token = TokenCache::getApplicationToken('application');
        $this->api = 'https://graph.microsoft.com/beta/reports/credentialUserRegistrationDetails';
    }


    // init lazy collection
    public function load($filter)
    {
        return LazyCollection::make(function () use ($filter) {
            $this->filters = $filter;
            $count = 0;
            $this->api = $this->applyApiFiltering($this->api);
            $nextPage = $this->api;

            while (!is_null($nextPage)) {
                list($data, $nextPage) = $this->getNextPage($nextPage, $this->token);

                $data = $this->offsetKeys($data, $count);

                $count += sizeof($data);

                yield from $data;
            }
        });
    }

    // run rest calls with pageination links
    protected function getNextPage(string $nextPage, string $token)
    {
        $response = Http::withToken(Crypt::decryptString($token))->get($nextPage);

        $data = $response->json();

        $nextPage = $data['@odata.nextLink'] ?? null;
        $value = $data['value'] ?? [];
        return array($value, $nextPage);
    }

    private function offsetKeys(array $data, int $count)
    {
        $newData = [];

        foreach ($data as $key => $value) {
            $newData[$key + $count] = $value;
        }

        return $newData;
    }

}

WithRestFiltering Trait:

namespace App\Traits;

use Illuminate\Support\Arr;

trait WithRestFiltering
{
    public function updatedFilters()
    {
        $this->resetPage();
    }

    protected function applyApiFiltering($query): string
    {
        $queryFilter = [];
        foreach ($this->filters as $filter => $value) {
            switch ($filter) {
                case
                    $filter == 'search' && !empty($value) :
                    $queryFilter[] = 'startswith(userPrincipalName, \'' . $value . '\')';
                    break;
                case
                    $filter == 'isRegistered' && !empty($value) :
                    $queryFilter[] = 'isRegistered eq ' . $value;
                    break;
                case
                    $filter == 'isEnabled' && !empty($value) :
                    $queryFilter[] = 'isEnabled eq ' . $value;
                    break;
                case
                    $filter == 'isCapable' && !empty($value) :
                    $queryFilter[] = 'isCapable eq ' . $value;
                    break;
                case
                    $filter == 'isMfaRegistered' && !empty($value) :
                    $queryFilter[] = 'isMfaRegistered eq ' . $value;
                    break;
            }
        }
        return $queryFilter ? $query . '?$filter=' . Arr::toFilter($queryFilter) : $query;

    }
}

Arr::toFilter macro in AppServiceProvider:

Arr::macro('toFilter', function ($array,$operator = 'and') {
    return implode(' '.$operator.' ', array_map(fn($value) => $value, $array, array_keys($array)));
});

[patrickbrouwers]

Lazy collection is most likely slower indeed, however indeed more stable. Perhaps split the process in 2; store in db and use the FromQuery concern which will add the value in chunks (fastest option)