[5.x]: 4x Slower Product & Variant Queries from Craft Commerce 4 → 5

[apitel]

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

1. Go to Control Panel → Commerce → Orders → New Order
2. Select "Add a line item"
3. 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

[linear[bot]]

PT-2121 [5.x]: 4x Slower Product & Variant Queries from Craft Commerce 4 → 5

[nfourtythree]

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 [email protected]

We can then take a further look into this issue.

Thanks!

[apitel]

Composer files and DB backups for 4.x and 5.x provided on 8/28.

[nfourtythree]

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!

[apitel]

We saw about a 20% improvement in speed after this update, however, the same queries are still about 4x slower than in Craft 4.