[5.x]: 4x Slower Product & Variant Queries from Craft Commerce 4 → 5
Closed this issue · 5 comments
What happened?
Description
We run a Craft Commerce site with a high number of variants (400,000+) and various product configurators that filter through these variants. With identical products and variants, we noticed ~ 4x slower query speeds when upgrading from Craft 4 → Craft 5. At first we figured we needed some better optimizations in our configurators, however, we then noticed the same slowdown in the Control Panel, when trying to manually adding variants to an order. We confirmed anywhere that Craft Commerce is querying products or variants, we are experiencing the same 4x slowdown.
In reviewing some of the queries performed by Craft Commerce, it appears that Craft 4 queries elements just by id, whereas Craft 5 has a much more complex element query.
v4 Query
v5 Query
Maybe unrelated, but we noticed some formatting code that repeats the variant query. When commenting this code out (as shown in the screenshot), there is a significant performance improvement:
Steps to reproduce
- Go to Control Panel → Commerce → Orders → New Order
- Select "Add a line item"
- Enter a product SKU
We can provide a copy of our database from both v4 and v5 to compare, if helpful.
Expected behavior
We expect query speeds to be similar between Craft Commerce 4 and Craft Commerce 5.
Actual behavior
4x (or more) slower performance when querying products and variants in Craft Commerce 5.
Craft CMS version
5.3.4
Craft Commerce version
5.0.16.2
PHP version
8.3
Operating system and version
No response
Database type and version
MySQL 8.0.39
Image driver and version
No response
Installed plugins and versions
Hi @apitel
Thank you for the feedback it would be great if you could send over your composer files and DB backups for both 4.x and 5.x to support@craftcms.com
We can then take a further look into this issue.
Thanks!
Composer files and DB backups for 4.x and 5.x provided on 8/28.
Hi @apitel
Thank you for providing the files it was helpful to get an understanding of what was going on.
Commerce 5.1.0 has now been released containing fixes and updates for improving the performance of purchasable queries.
Please update to this version and you should begin to see an improvement.
If you have any further problems, please reopen this issue.
Thanks!
We saw about a 20% improvement in speed after this update, however, the same queries are still about 4x slower than in Craft 4.