Optimizing export

[kirya-dev]

For export large data need call flush() in StreamedResponse.
its allow clear buffer and not use extra memory.
Todo as feature of framework?

My solution:

# config/services.yml
services:
    app.export.writer.csv:
        class: App\Writer\FlushWriterDecorator
        decorates: sonata.exporter.writer.csv

    app.export.writer.json:
        class: App\Writer\FlushWriterDecorator
        decorates: sonata.exporter.writer.json

    app.export.writer.xml:
        class: App\Writer\FlushWriterDecorator
        decorates: sonata.exporter.writer.xml

    app.export.writer.xls:
        class: App\Writer\FlushWriterDecorator
        decorates: sonata.exporter.writer.xls

<?php
declare(strict_types=1);

namespace App\Writer;

use Sonata\Exporter\Writer\TypedWriterInterface;

class FlushWriterDecorator implements TypedWriterInterface
{
    private const FLUSH_EVERY = 1000;

    private int $position;
    private TypedWriterInterface $writer;

    public function __construct(TypedWriterInterface $writer)
    {
        $this->writer = $writer;
    }

    public function open(): void
    {
        $this->writer->open();

        $this->position = 0;
    }

    public function write(array $data): void
    {
        $this->writer->write($data);

        $this->position++;
        if (0 === ($this->position % self::FLUSH_EVERY)) {
            flush();
        }
    }

    public function close(): void
    {
        $this->writer->close();
    }

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

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

[VincentLanglet]

Hi, this feature could be great.
Wanna start a Pr ? :)

[kirya-dev]

Yes!
I have next questions:

1. Is optional decorator?
2. Value of FLUSH_EVERY pass by parameters for more flexability?
   If null (on default) no need use decorator

[VincentLanglet]

Yes!

I have next questions:

1. Is optional decorator?

2. Value of FLUSH_EVERY pass by parameters for more flexability?

If null (on default) no need use decorator

1. dont know 😅

2. this value could come from a sonata exporter config parameter indeed. I'm not sure if we should keep null as default value or if we can abritrary chose a number.

[kirya-dev]

There have other problem with get large data from doctrine. My solve this problem by using many paginated queries.
Need solve this also in this bundle?

Im my test combine both optimizing dont eat memory more than 60MB.. Exported file size it was 650MB

[kirya-dev]

This improved version of DoctrineORMQuerySourceIterator

<?php
declare(strict_types=1);

namespace App\Export\Source;

use Doctrine\ORM\Query;
use Sonata\Exporter\Source\DoctrineORMQuerySourceIterator;

final class PaginatedDoctrineORMQuerySourceIterator extends DoctrineORMQuerySourceIterator
{
    private const PAGE_SIZE = 1000;

    private int $page = 0;

    public function __construct(Query $query, array $fields, string $dateTimeFormat = 'r')
    {
        parent::__construct($query, $fields, $dateTimeFormat);

        $this->query->setMaxResults(self::PAGE_SIZE);
        $this->query->setFirstResult(0);
    }

    public function next(): void
    {
        $this->iterator->next();

        if (!$this->iterator->valid()) {
            $this->page++;
            $this->query->setFirstResult($this->page * self::PAGE_SIZE);
            $this->query->getEntityManager()->clear();

            $this->iterator = null;
            $this->rewind();
        }
    }
}

[kirya-dev]

That screenshot indicates low memory usage when exporting big data. Im apply both optimization.
Downloaded file with size is 637.4 MB

[VincentLanglet]

This improved version of DoctrineORMQuerySourceIterator

<?php

declare(strict_types=1);



namespace App\Export\Source;



use Doctrine\ORM\Query;

use Sonata\Exporter\Source\DoctrineORMQuerySourceIterator;



final class PaginatedDoctrineORMQuerySourceIterator extends DoctrineORMQuerySourceIterator

{

    private const PAGE_SIZE = 1000;



    private int $page = 0;



    public function __construct(Query $query, array $fields, string $dateTimeFormat = 'r')

    {

        parent::__construct($query, $fields, $dateTimeFormat);



        $this->query->setMaxResults(self::PAGE_SIZE);

        $this->query->setFirstResult(0);

    }



    public function next(): void

    {

        $this->iterator->next();



        if (!$this->iterator->valid()) {

            $this->page++;

            $this->query->setFirstResult($this->page * self::PAGE_SIZE);

            $this->query->getEntityManager()->clear();



            $this->iterator = null;

            $this->rewind();

        }

    }

}

You need to take in account the fact that the query may already have a first result and a max result.

For example: If I want all the result from 3 to 2500, you'll have to do 3 to 1000 then 1000 to 2000 then 2000 to 2500.

[VincentLanglet]

Doesnt work if there is 3000 result in my example, because you'll export all of them even if I would like to stop at 2500.

In construct
OriginalFirstResult = query->getfirstresult();
OriginalMaxResult = query->getMaxResult()

Query->setMaxResult(min(originalMaxResult, originalFirstResult + page size))

Then, the check would be
If iterator not valid and originalMaxResult > currentMaxResult (where currentMaxResult = query->getMaxResult())

SetMaxResult((min(originalMaxResult, currentMaxResult + page size))
SetFirstResult(currentFirstResult + page size)

With this formula you dont need the page property, neither the originalFirstResult property ; just the originalMaxResult property.

I'm on phone, if it's not clear enough, I'll improve my message in two days.

Edit: did you remove your message or I'm crazy ? 😂

[kirya-dev]

If we use pagination, then we may not be sure that the next query will return next data.
For example, if you sort in descending order by creation date (from new to old), then at one point it may turn out that there will be several duplicate records in the upload. Also, the database does not guarantee that the data will be selected in the same sequence using limits and offsets.
This can be solved by resetting the ascending sorting ID. And in the next request, take records strictly older than this value, or vice versa.

[kirya-dev]

I think we just left note in README that for using paginate export need not changed and ordered data.

[VincentLanglet]

If we use pagination, then we may not be sure that the next query will return next data.
For example, if you sort in descending order by creation date (from new to old), then at one point it may turn out that there will be several duplicate records in the upload. Also, the database does not guarantee that the data will be selected in the same sequence using limits and offsets.
This can be solved by resetting the ascending sorting ID. And in the next request, take records strictly older than this value, or vice versa.

Oh yes indeed. Maybe @greg0ire can help about this kind of problem.
But anyway, currently the export doesn't work when there is too much data, so any improvement is good to take.

If you start a PR, the reviews can lead us to the right way ;)

[greg0ire]

You can fix such issues with cursor based pagination: https://use-the-index-luke.com/no-offset

[kirya-dev]

So:

1. Validate: Initial query must havnt offset & limit.
2. Validate: must have order by in initial query.
3. Add limit (PAGE_SIZE) for query.
4. Extract seek field from order by.
5. Fetch first page.
6. Do export.

If count(results) = PAGE_SIZE:

7. Add seek clause. If query contains where clause wee need wrap it into parenthesis and add and seek clause. (Avoid repeat this step)
8. Extract last seek value for previous results. (From last results)
9. Fetch next results.
10. Go to 6

[VincentLanglet]

Why 1 ? Cant you use the max result and the offset provided ?

Why 2 ? Cant you add one order if needed ?