shopware / shopware/shopware

Idea for improving the performance of the repository iterator

Open
#8,846 1 comment 1 reaction 0 assignees View on GitHub
component/core domain/framework priority/high
Dominant language
PHP
Stars
3.4k
Forks
1.2k
Avg merge
2d 23h
Merged PRs (30d)
433

Description

### Technical TODO

Using the offset + limit approach for iterating over sufficiently large tables (products in my case) takes a very long time.

My idea would be to leverage the auto_increment field (when available) to adjust iterating as follows:
- initially fetch max(auto_increment) as the iteration limit
- replace the the offset/limit logic in the criteria to use something like this:
```
$criteria->setFilter(
'increment',
new MultiFilter(
MultiFilter::CONNECTION_AND,
[
new RangeFilter('autoIncrement', [RangeFilter::GTE => $this->offset]),
new RangeFilter('autoIncrement', [RangeFilter::LTE => $this->offset + 10000]), // 10000 is just an example for the chunksize
]
));
```
- in RepositoryIterator::fetch() return null under the condition that the current offset has surpassed the initially fetched max(auto_increment)

This is just a rough idea and don't know if this approach is plausible in the broader shopware context. Of course this would increase the total number of database queries and if there are gaps in the auto_increment field, there are possibly empty chunks.
My initial testing made the iteration go from ~24h to ~30min over 1.9 million products (filters: active and visible in a certain saleschannel).

a query like this takes ~17 seconds for me
```
SELECT
`product`.`id`,
`product`.`product_number`,
`product`.`auto_increment`
FROM
`product`
LEFT JOIN `product_visibility` `product.visibilities` ON `product`.`id` = `product.visibilities`.`product_id`
AND `product`.version_id = `product.visibilities`.product_version_id
WHERE (`product`.`version_id` = 0x0FA91CE3E96A4BC2BE4BD9CE752C3425)
AND((`product.visibilities`.`sales_channel_id` = 0x47A5CD2B2BFC4C0E823CA4DA307B88AE
AND `product`.`active` = 1
AND(`product`.`auto_increment` > 26776)))
GROUP BY
`product`.`id`
ORDER BY
MIN(`product`.`auto_increment`) ASC
LIMIT 500;
```
while something like this only takes ~330ms (even with a 20x chunksize)
```
SELECT
`product`.`id`,
`product`.`product_number`,
`product`.`auto_increment`
FROM
`product`
LEFT JOIN `product_visibility` `product.visibilities` ON `product`.`id` = `product.visibilities`.`product_id`
AND `product`.version_id = `product.visibilities`.product_version_id
WHERE (`product`.`version_id` = 0x0FA91CE3E96A4BC2BE4BD9CE752C3425)
AND((`product.visibilities`.`sales_channel_id` = 0x47A5CD2B2BFC4C0E823CA4DA307B88AE
AND `product`.`active` = 1
AND(`product`.`auto_increment` > 26776)
AND(`product`.`auto_increment` < 36776)))
GROUP BY
`product`.`id`
ORDER BY
MIN(`product`.`auto_increment`) ASC;
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.