Slow performance when exporting CSV from the UI listing component with large dataset

Preconditions and environment

Summary

Slow performance when exporting CSV from the UI listing component with large dataset.

Steps to reproduce

1. Place 10 orders
2. Go to the admin
3. Navigate SALES > Orders
4. Add this line $countSelect->where('SLEEP(1) = 0'); in Magento\Framework\Data\Collection\AbstractDb::getSelectCountSql after the resets for the Order Grid instance.

public function getSelectCountSql()
{
        // ...    
        $countSelect->reset(\Magento\Framework\DB\Select::LIMIT_OFFSET);
        $countSelect->reset(\Magento\Framework\DB\Select::COLUMNS);

        if ($this instanceof \Magento\Sales\Model\ResourceModel\Order\Grid\Collection) {
            $countSelect->where('SLEEP(1) = 0');
        }
        // ... 
}

5. Change the $pageSize from 200 to 1, in Magento\Ui\Model\Export\ConvertToCsv::__construct.
6. In the order grid, select Export > CSV > Export.

Expected result

The processing will take more than 15-16 seconds. Also, multiple calls for Magento\Framework\Data\Collection\AbstractDb::getSelectCountSql.

Actual result

The processing should take more than 4-5 seconds. Also 2-3 calls for Magento\Framework\Data\Collection\AbstractDb::getSelectCountSql.

Additional information

AC-9607 moved the $searchResult->setTotalCount($totalCount); to after $searchResult->getItems();. It causes the unnecessary call for Magento\Framework\Data\Collection\AbstractDb::getSelectCountSql on each interaction making the CSV export slow.

Release note

No response

Triage and priority

• Severity: S0 - Affects critical data or functionality and leaves users without workaround.
• Severity: S1 - Affects critical data or functionality and forces users to employ a workaround.
• Severity: S2 - Affects non-critical data or functionality and forces users to employ a workaround.
• Severity: S3 - Affects non-critical data or functionality and does not force users to employ a workaround.
• Severity: S4 - Affects aesthetics, professional look and feel, “quality” or “usability”.

Reproduce the admin Sales > Orders CSV export using the steps provided, then inspect Magento\Framework\Data\Collection\AbstractDb::getSelectCountSql and Magento\Ui\Model\Export\ConvertToCsv::__construct. Review commit AC-9607 and trace the count calls during export; done means the unnecessary count calls are removed and large-dataset CSV export performance is improved.