An optimization for the product attributes lookup table is coming

konamiman Avatar
konamiman

•

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.

Keep yourself in the loop!

This field is hidden when viewing the form
This field is hidden when viewing the form
This field is hidden when viewing the form

3 responses to “An optimization for the product attributes lookup table is coming”

  1. Diego Zanella Avatar
    Diego Zanella

    This is an interesting update. I’ve been advocating for this kind of approach for a decade, and, while I’m aware of potential drawbacks, the advantages are huge, compared to “RBAR” loops. It seems that the core WooCommerce development logic and I getting more “aligned” by the day. 😁

    Reply
  2. GasserStance Avatar
    GasserStance

    Will this affect the forcible update of single PA table entries for a single product using code?

    I use the “woocommerce advanced bulk edit” plugin almost exclusively to populate and publish products, and found that it lagged horribly behind the customers requirement for filtering products using attributes, so I use the following custom function to manually enforce PA cache table updates on publish events to keep the table warmed for new products to appear in attribute filtered results:

    function update_pa_lookup_table_only_on_pub_new( $new_status, $old_status, $post ) {
    if (get_post_type($post) == “product”){
    if ( $new_status == ‘publish’ && $old_status != ‘publish’ ) {
    $lookupDataStore = new LookupDataStore();
    $lookupDataStore->create_data_for_product($post);
    }
    }
    }
    add_action(‘transition_post_status’, ‘update_pa_lookup_table_only_on_pub_new’, 10, 3 );

    Any indication if the new method might affect this or has the query code behind these DataStore actions been grandfathered in?

    Cheers,

    Reply
    1. Jorge Torres Avatar
      Jorge Torres

      Hey there! Thanks for reaching out. The code you shared should still work fine. If you want to take advantage of the improved queries you can pass true as second argument to create_data_for_product() but given you’re running this for one product at a time, it won’t necessarily make a ton of difference and the caveats about extension compatibility apply. Let us know if you have any other questions!

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *