Looking for Suggestions to Improve Bulk COGS Update Performance

[dineshinau]

Improving Bulk COGS Update Performance for 10,000+ WooCommerce Products

I've been working on improving the performance of bulk Cost of Goods Sold updates in Bulk COGS Editor for WooCommerce, particularly for stores containing tens of thousands of products and variations.

The processor intentionally continues to use WooCommerce CRUD APIs for writing COGS values rather than updating the database directly.

The goal is to maintain compatibility with WooCommerce caches, hooks, extensions, and future storage changes while making large bulk operations considerably faster.

Performance so far

Using approximately 10,000 products as the benchmark, processing time has improved through several rounds of optimization:

Stage	Time for ~10,000 products	Main change
Initial implementation	50–60 minutes	Standard Action Scheduler batches and per-product WooCommerce CRUD processing
First optimization	40–42 minutes	Reduced repeated work inside the processing loop, optimized batch scanning/counting, buffered progress state, and reduced unnecessary reads between individual product updates
Current implementation	25–28 minutes	WooCommerce product-transient invalidation is deferred per batch, while product/post/meta/term data is cache-primed before processing
1.2.1	15-16 minutes	Reduce history journal reads from three queries to one for each newly recorded product.

The earlier optimization work reduced repeated counting and scanning work and separated ID retrieval from total-count queries instead of repeatedly doing both during snapshot collection.

The worker loop was also optimized so it does not reload the complete operation state before every individual product. Progress counters are buffered and checkpointed while individual product outcomes remain durable.

The latest major improvement came from reducing the amount of cache invalidation WooCommerce performs while many products are being updated.

The processor now uses WooCommerce's internal ProductTransientsDeferrer to defer repeated product-transient deletion until a batch finishes instead of performing the same invalidation work repeatedly for individual products.

Before processing pending products in a chunk, the implementation also primes WordPress post, metadata, term, and parent-product caches. This is particularly useful for variations because their parent-product data may otherwise need to be loaded repeatedly.

Performance progression

50–60 min → 40–42 min → 25–28 min

This represents roughly a 50% reduction in total processing time compared with the original implementation while still keeping WooCommerce responsible for the actual product writes.

Current architecture

The operation runs through Action Scheduler using persisted chunks of product IDs.

The current default batch size is 50 products, although the batch size is filterable.

Processing is resumable and keeps per-product results so an interrupted or retried worker does not blindly process products that have already completed.

The snapshot process has also been optimized to avoid repeatedly using growing pagination offsets. It now scans forward using a product-ID cursor, reuses previously persisted chunks after interruptions, and caches search matching for an operation.

At this point, the largest remaining performance cost appears to be the per-product WooCommerce CRUD write path itself.

Each product still needs to be:

1. Loaded through WooCommerce.
2. Have its new COGS value calculated.
3. Have the COGS value updated.
4. Be saved through the WooCommerce CRUD layer.
5. Trigger the necessary WooCommerce hooks and cache synchronization.

What I'm looking for

I'm looking for ideas to reduce the remaining 25–28 minute runtime for approximately 10,000 products without bypassing WooCommerce's data layer.

Some areas I'm interested in exploring:

• Reducing the cost of repeated WC_Product loading and saving.
• Reducing database queries per product.
• Avoiding unnecessary metadata writes when the COGS value has not changed.
• Further reducing cache invalidation during bulk operations.
• Deferring additional WooCommerce synchronization safely until the end of a batch.
• Improving batch sizes or worker execution time.
• Reducing Action Scheduler overhead.
• Processing more products within the same WordPress request without causing memory or timeout issues.
• More efficient handling of product variations and their parent products.
• WooCommerce Data Store APIs or internal APIs suitable for large-volume updates.
• Safe bulk-write approaches that still preserve WooCommerce hooks and cache consistency.

Direct SQL updates would obviously be significantly faster, but I would prefer not to update WooCommerce product/meta tables directly if that means bypassing CRUD hooks, cache invalidation, extension integrations, or future WooCommerce storage changes.

Target

The longer-term target is to make this practical for stores with:

10,000 → 50,000 → 100,000+ products and variations

Ideally, I'd like to bring a 10,000-product bulk update substantially below the current 25–28 minute range while preserving:

• WooCommerce compatibility
• Data integrity
• Extension compatibility
• Resumable background processing
• Reliable per-product results
• Safe cancellation and recovery

If anyone has experience optimizing large WooCommerce imports, product synchronization, bulk editors, or high-volume CRUD operations, I'd appreciate suggestions, profiling results, or examples of WooCommerce APIs/patterns that could eliminate another significant portion of the per-product overhead.

[dineshinau]

Latest benchmark update

A new benchmark run completed the update of 10,000 products in 21 minutes 34 seconds, with:

• 10,000 products updated
• 0 products skipped
• 0 products failed
• Total elapsed time: 00:21:34

This is the fastest result observed so far.

The performance progression is now approximately:

50–60 min → 40–42 min → 25–28 min → 21 min 34 sec

Compared with the original 50–60 minute runtime, this latest run represents roughly a 57–64% reduction in processing time, while the updates are still being performed through the WooCommerce CRUD/data layer.

Since this is currently a single latest run, I’ll continue benchmarking to see whether the ~21-minute runtime is consistently reproducible across repeated runs and larger product datasets.