[5.x]: Inventory::getInventoryFulfillmentLevels() causing slowdowns on cart updates.

[joshuapease]

Description

We've noticed some really big slowdowns around inventory and cart updates.

LineItem::getFulfilledTotalQuantity() which subsequently calls Inventory::getInventoryFulfillmentLevels() calls the same query to commerce_inventorytransactions for each line item.

This project has 600,000+ skus, which makes this query especially slow.

We also don't track inventory on this project, so it seems like their might be some efficiency gains if the query could just be skipped for purchasables that don't have $inventoryTracked = true

I noticed this PR was opened recently too

• https://github.com/craftcms/commerce/pull/4055/files

Steps to reproduce

1. Have a high number of Variants in Craft (600,000+)
2. Add multiple items to cart

Expected behavior

• Only one call to Inventory::getInventoryFulfillmentLevels()
• Ideally this query is bypassed when a purchasable doesn't track inventory.

Actual behavior

• Calls to Inventory::getInventoryFulfillmentLevels() grow as line items increase

Craft CMS version

5.7.4

Craft Commerce version

5.3.13

PHP version

8.3

Operating system and version

DDEV

Database type and version

8.0.40

Image driver and version

No response

Installed plugins and versions

[linear[bot]]

PT-2825 [5.x]: Inventory::getInventoryFulfillmentLevels() causing slowdowns on cart updates.

[lukeholder]

Thanks for reporting this. It seems this is only affecting carts loaded over Ajax (in your case Sprig).

I have made a fix that should help speed things up for you (and I am working on some more).

To get the fix early, Make sure you have already upgraded to Commerce 5.4.0 and then change your craftcms/commerce requirement in composer.json to:

"require": {
  "craftcms/commerce": "5.x-dev#237478671bddf02b71066cb65f06d66511e0a13e as 5.4.0",
  "...": "..."
}

Then run composer update.

We will update this ticket once the release is out.

[joshuapease]

Thanks @lukeholder that made an improvement

[johnnynotsolucky]

We found that memoizing the inventory levels, transactions and fulfillments significantly improved things with 500k+ inventory items.

The cause of problem seemed to be that those queries executed quite a few times for each cart operation. It was significantly more when we would add multiple items to the cart at once.

[lukeholder]

I have made another small fix that should help speed things up for you.

To get the fix early, Make sure you have already upgraded to Commerce 5.4.3 and then change your craftcms/commerce requirement in composer.json to:

"require": {
  "craftcms/commerce": "5.x-dev#3a6460ba47afe1fd14b04f2302df9ff1ce7479b5 as 5.4.3",
  "...": "..."
}

Then run composer update.

We will update this ticket once the release is out. Thanks!

[brandonkelly]

Commerce 5.4.4 is out with that latest change.