Performance optimization for export chunks: Using cursor pagination

[julienarcin]

Hello,

It seems that the chunk exports are using the MySQL OFFSET feature.
It can have a performance cost on big exports, since MySQL needs to go through all records internally to offset the results.

That's why Laravel introduced the concept of cursor pagination: https://laravel.com/docs/8.x/pagination#cursor-pagination

In Maatwebsite\Excel\Jobs\AppendQueryToSheet:

/**
 * @param  Writer  $writer
 *
 * @throws \PhpOffice\PhpSpreadsheet\Exception
 * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
 */
public function handle(Writer $writer)
{
    (new LocalizeJob($this->sheetExport))->handle($this, function () use ($writer) {
        $writer = $writer->reopen($this->temporaryFile, $this->writerType);

        $sheet = $writer->getSheetByIndex($this->sheetIndex);

        $query = $this->sheetExport->query()->forPage($this->page, $this->chunkSize);

        $sheet->appendRows($query->get(), $this->sheetExport);

        $writer->write($this->sheetExport, $this->temporaryFile, $this->writerType);
    });
}

The forPage method is calling the query builder under the hood (Illuminate\Database\Query\Builder)

/**
 * Set the limit and offset for a given page.
 *
 * @param  int  $page
 * @param  int  $perPage
 * @return $this
 */
public function forPage($page, $perPage = 15)
{
    return $this->offset(($page - 1) * $perPage)->limit($perPage);
}

I think we don't need to know the number of pages, we only need to keep the current cursor in the AppendQueryToSheet class, and dispatch jobs using cursors in the Maatwebsite\Excel\QueuedWriter class.

Let me know your thought about this.

[patrickbrouwers]

AppendQueryToSheet Job (and queued chunk exports ) will disappear in the next major release (in favor of normal queued exports) so I think it's better to have another look at this when other things under the hood have changed first.

[julienarcin]

OK I understand 👍

[patrickbrouwers]

Thanks for the idea though :)

[markwalet]

The problem is still there. As is the AppendQueryToSheet job. What is the alternative for this that doesn't have degraded performance on large datasets? I'm looking to export around 250k rows, but with each page taking more than a minute, this can take a while or even timeout.

How does a "normal queued export" work in this case? Or is this not implemented yet?