An optimization for the product attributes lookup table is coming

A couple of years ago, we introduced the product attributes lookup table in WooCommerce as a way to fix an issue with the search/filtering of variable products having out of stock variations. And while the mechanism works, we have received reports of poor performance of table updates involving complex products.

WooCommerce 9.1, scheduled to launch on July 9, 2024, will include two significant improvements regarding this lookup table:

• An optional optimized alternative way of filling/updating the table. Pull request here.
• A new set of CLI tools to manage the table regeneration/updates. Pull request here.

Optimizing the table updates

What has changed?

Initially, the code relied on the wc_get_product function to collect product information, mirroring our recommendation for extension developers. However, its performance characteristics aren’t ideal for the scenarios we’re dealing with here, especially when iterating across a large volume of complex products (like those with hundreds of variations).

To improve performance in these complex scenarios, we’ve implemented several optimizations centered around highly optimized database queries. These enhancements significantly speed up the table update and regeneration process, reducing processing times from a few seconds to mere millisecond in instances involving products with extensive variation sets.

Do I need to do something?

Yes, but only if you want the optimization to be applied to your store.

While the structure of the lookup table remains unchanged, and the creation of lookup data stays consistent, opting into this new optimized code is entirely voluntary. We anticipate this enhancement to benefit all WooCommerce stores, yet we’ve chosen caution by making its adoption optional.

This means that if you want your store to use the optimized code for lookup table updates, you need to explicitly enable it by going to WooCommerce > Settings > Product > Advanced and enabling the “Optimized updates” setting:

Understanding the Optimized updates setting

• “Uses much more performant queries to update the lookup table, but may not be compatible with some extensions”. While we’re unaware of any incompatibilities, switching to direct database queries from standard WordPress/WooCommerce functions carries inherent risks, such as potentially missing hook executions. Despite this not appearing to be an issue, we prefer to allow users to initially opt in and out to ensure safety.
• “This setting only works when product data is stored in the posts table”. The new code requires products to be stored in the posts table, as it performs direct queries on standard WordPress tables. This aligns with WooCommerce’s default behavior, except when an extension modifies the products data store class. Rest assured, if such modifications occur, WooCommerce will detect that the standard products data store class is not in use, and will refrain from using the optimized code even if the “Optimized updates” setting is enabled.

The new CLI tools

A new set of command line tools to manage the product attributes lookup table updates/regeneration has been added. You can see the details in the pull request or by running wp help wc palt.

Perhaps the most interesting command is wp wc palt regenerate. This will synchronously regenerate the lookup table in command, and does not rely on scheduled actions running in the background. This may be useful for staging sites, or for production sites where you have plenty of resources and can time execution outside of peak hours.

Another interesting command is wp wc palt regenerate_for_product <product id>, which will recreate the lookup data for one single product and for all variations if they apply.

The regenerate and regenerate_for_product commands will use the optimized code by default, even if the Optimized updates setting is disabled; but you can add the --disable-db-optimization argument to the commands if you prefer to use the non-optimized code. Again, if products are not stored in the posts table as discussed previously, the optimized code will not be used, even without the --disable-db-optimization argument.

Increased batch size default

When the product attributes lookup table is being regenerated in the background, each regeneration scheduled action processes a batch of products. The size of this batch was 10 by default, but it’s possible to customize it with the woocommerce_attribute_lookup_regeneration_step_size filter. The regenerate command line tool uses this value too in order to determine how many products to process in each iteration of the regeneration loop, and has the --batch-size argument to customize it.

In WooCommerce 9.1 we are updating the default value of the batch size from 10 to 100. This is still a reasonable amount of products to process at once, and provides a further optimization for the regeneration process because it decreases the number of scheduled actions needed to complete the process, and consequently also reduces the number of calls to wc_get_products, which is an expensive operation.

You can still use the woocommerce_attribute_lookup_regeneration_step_size filter if you think that the new default batch size is either too small or too big for your needs.

Testing the feature

The feature will become available for official beta testing with the release of 9.1 beta, scheduled to become available on June 25, 2024.

If you are ready to dive in right now, you can use the nightly build of WooCommerce and upload woocommerce-trunk-nightly.zip into your test environment.

Do you have thoughts you want to share about the feature? You can continue to join the discussion in this GitHub issue.