[Performance] Orders: reduce the number of SQL queries required to persist a draft order during checkout by kalessil · Pull Request #63258 · woocommerce/woocommerce

kalessil · 2026-02-12T08:45:18Z

Submission Review Guidelines:

I have followed the WooCommerce Contributing Guidelines and the WordPress Coding Standards.
I have checked to ensure there aren't other open Pull Requests for the same update/change.
I have reviewed my code for security best practices.
Following the above guidelines will result in quick merges and clear and detailed feedback when appropriate.

Changes proposed in this Pull Request:

Re-iterate on #63118 (#63110, WOOPLUG-6243)

Further reduce the number of SQLs when persisting an order (check-out page in focus).

With HPOS compatibility mode

188 -> 172 SQLs (-9% / -16 SQLs; without object cache)
127 -> 115 SQLs (-9% / -12 SQLs; with object cache)

Without HPOS compatibility mode

141 -> 125 SQLs (-11% / -16 SQLs; without object cache)
69 -> 57 SQLs (-17% / -12 SQLs; with object cache)

Refresher from #63118: the eliminated SQLs are fast individually (so locally it's a micro-optimization), but the reduction in number allows us to stretch DB capacity further, when it's under pressure, SQLs are getting queued, and we have different execution timings.

Technical aspects of the changes:

get_items: fires a filter and a data store reads, a bit heavy - performed audit and applied micro-optimization (housekeeping during the exploration phase)
added options cache priming in \WC_Shipping::get_shipping_method_class_names
addressed usermeta cache invalidate/repopulate loops in wc_delete_shop_order_transients (for checkout draft orders)

How to test the changes in this Pull Request:

Functional testing:

Using this branch test checkout completion and order status change in admin

Numbers verification:

Ensure the testing site has Query Monitor or a similar plugin installed, and object cache plugin of your choice
Using this branch, add a product to the cart and navigate to checkout
Refresh checkout page until the number of SQLs stabelizes and memorize/note SQLs number (repeat with and without cache plugin enabled - numbers will differ)
Switch to the trunk
Refresh checkout page until the number of SQLs stabelizes and memorize/note SQLs number (repeat with and without cache plugin enabled - numbers will differ)
Verify the decrease in number of SQLs

…-calls

github-actions · 2026-02-12T09:36:44Z

Testing Guidelines

Hi @jorgeatorres @albarin ,

Apart from reviewing the code changes, please make sure to review the testing instructions (Guide) and verify that relevant tests (E2E, Unit, Integration, etc.) have been added or updated as needed.

Reminder: PR reviewers are required to document testing performed. This includes:

🖼️ Screenshots or screen recordings.
📝 List of functionality tested / steps followed.
🌐 Site details (environment attributes such as hosting type, plugins, theme, store size, store age, and relevant settings).
🔍 Any analysis performed, such as assessing potential impacts on environment attributes and other plugins, conducting performance profiling, or using LLM/AI-based analysis.

⚠️ Within the testing details you provide, please ensure that no sensitive information (such as API keys, passwords, user data, etc.) is included in this public issue.

coderabbitai · 2026-02-12T09:46:02Z

No actionable comments were generated in the recent review. 🎉

📝 Walkthrough

Walkthrough

Performance-focused changes that reduce repeated data loads during order processing: cache line items and data stores before loops, pre-prime legacy shipping option caches, add guards around order existence, add a changelog entry, and add a unit test for usermeta purging.

Changes

Cohort / File(s)	Summary
Changelog `plugins/woocommerce/changelog/performance-63118-reduce-sqls-number-iteration-2`	New changelog entry documenting the performance improvement that reduces SQL queries when persisting draft orders at checkout.
Order Core `plugins/woocommerce/includes/class-wc-order.php`	Cache `get_items()` result in `needs_processing()` to avoid repeated calls during line item checks.
Shipping Core `plugins/woocommerce/includes/class-wc-shipping.php`	Pre-prime legacy shipping option caches via `wp_prime_option_caches()` before iterating legacy methods to reduce option load calls.
Order Functions `plugins/woocommerce/includes/wc-order-functions.php`	Pre-fetch line items and data stores before loops, add guards in `wc_delete_shop_order_transients()` to verify order validity and selectively purge metas, reuse pre-loaded download data store in refund flows, and update a PHPDoc return type to `array<string,string>`.
Static Analysis Baseline `plugins/woocommerce/phpstan-baseline.neon`	Adjusted PHPStan baseline count for an `is_a` parameter type message related to `includes/wc-order-functions.php`.
Tests `plugins/woocommerce/tests/php/includes/wc-order-functions-test.php`	Added `test_wc_delete_shop_order_transients_usermeta_purge()` to verify usermeta purging behavior for orders; imports user utilities used by the test.

Estimated code review effort

🎯 3 (Moderate) | ⏱️ ~20 minutes

🚥 Pre-merge checks | ✅ 3

✅ Passed checks (3 passed)

Check name	Status	Explanation
Title check	✅ Passed	The title accurately summarizes the main objective of the PR: reducing SQL queries during draft order persistence at checkout, which directly reflects the core changes across multiple files.
Description check	✅ Passed	The description is directly related to the changeset, detailing the performance improvements, specific SQL reductions with/without HPOS and object cache, and technical aspects of the optimizations made.
Docstring Coverage	✅ Passed	Docstring coverage is 100.00% which is sufficient. The required threshold is 80.00%.

_{✏️ Tip: You can configure your own custom pre-merge checks in the settings.}

✨ Finishing Touches

📝 Generate docstrings

🧪 Generate unit tests (beta)

Create PR with unit tests
Post copyable unit tests in a comment
Commit unit tests in branch performance/63118-reduce-sqls-number-iteration-2

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

_{Comment @coderabbitai help to get the list of available commands and usage tips.}

github-actions · 2026-02-12T09:49:49Z

Test using WordPress Playground

The changes in this pull request can be previewed and tested using a WordPress Playground instance.
WordPress Playground is an experimental project that creates a full WordPress instance entirely within the browser.

Test this pull request with WordPress Playground.

Note that this URL is valid for 30 days from when this comment was last updated. You can update it by closing/reopening the PR or pushing a new commit.

coderabbitai

Actionable comments posted: 1

🤖 Fix all issues with AI agents

In `@plugins/woocommerce/includes/wc-order-functions.php`:
- Around line 512-521: The backward-compatibility check using
array_key_exists(OrderStatus::CHECKOUT_DRAFT, wc_get_order_statuses()) fails
because wc_get_order_statuses() returns prefixed keys (e.g.
'wc-checkout-draft'), and OrderInternalStatus::CHECKOUT_DRAFT does not exist;
fix by checking for the prefixed key string instead (e.g. 'wc-checkout-draft')
or adjust the guard to transform OrderStatus::CHECKOUT_DRAFT into the prefixed
form before calling wc_get_order_statuses(); update the conditional that sets
$purge_usermeta (and any references to OrderStatus::CHECKOUT_DRAFT) to use the
correct prefixed key or mapping logic so the backward-compatibility escape hatch
works as intended.

…ion-2

jorgeatorres

LGTM. Thank you @kalessil!

…ion-2

…rsist a draft order during checkout (#63258) Optimize purging customers' usermeta in `wc_downloadable_product_permissions`, which is called multiple times during order save. The optimization leverages reads as guard conditions, as reads are performed from the in-memory cache until deletions are necessary. Previously unguarded deletions were causing cache invalidation and re-read from DB.

kalessil added 10 commits February 5, 2026 15:30

Orders: address excessive get_items method calls.

73c9a31

Orders: partial revert.

6432a49

Orders: partial revert.

df34a00

Orders: partial revert.

e2b3f2c

Orders: cleanup.

94b2d42

Orders: cleanup.

ee9d7c1

Orders: cleanup.

7e99272

Order: further reduce the number of SQLs on checkout page.

bcb5338

Merge branch 'trunk' into performance/63110-avoid-excessive-get_items…

5155252

…-calls

Merge branch 'trunk' into performance/63110-avoid-excessive-get_items…

ddd8aca

…-calls

kalessil self-assigned this Feb 12, 2026

github-actions Bot added the plugin: woocommerce Issues related to the WooCommerce Core plugin. label Feb 12, 2026

kalessil mentioned this pull request Feb 12, 2026

[WIP] Orders: address unneccesary get_items method calls #63136

Closed

kalessil added 2 commits February 12, 2026 10:05

Order: please CI.

2a00610

Order: code style fixes.

3bbe4cb

kalessil changed the title ~~[Performance] Orders: optimize user meta cleanup during order persistence~~ [Performance] Orders: optimize user meta cleanup during draft order persistence Feb 12, 2026

kalessil added 2 commits February 12, 2026 10:21

Order: fixed failing tests.

4d6804e

Order: add changelog entry.

1ffcb28

kalessil commented Feb 12, 2026

View reviewed changes

Comment thread plugins/woocommerce/includes/class-wc-shipping.php

Comment thread plugins/woocommerce/includes/wc-order-functions.php Outdated

kalessil changed the title ~~[Performance] Orders: optimize user meta cleanup during draft order persistence~~ [Performance] Orders: reduce the number of SQL queries required to persist a draft order Feb 12, 2026

kalessil changed the title ~~[Performance] Orders: reduce the number of SQL queries required to persist a draft order~~ [Performance] Orders:the number of SQL queries required to persist a draft order during checkout Feb 12, 2026

kalessil changed the title ~~[Performance] Orders:the number of SQL queries required to persist a draft order during checkout~~ [Performance] Orders: reduce the number of SQL queries required to persist a draft order during checkout Feb 12, 2026

kalessil requested review from albarin and jorgeatorres February 12, 2026 09:36

kalessil marked this pull request as ready for review February 12, 2026 09:42

woocommercebot requested a review from a team February 12, 2026 09:42

kalessil removed the request for review from a team February 12, 2026 09:44

kalessil added this to the 10.6.0 milestone Feb 12, 2026

jorgeatorres reviewed Feb 12, 2026

View reviewed changes

Comment thread plugins/woocommerce/includes/wc-order-functions.php Outdated

kalessil added 2 commits February 13, 2026 08:37

Order: address code review concerns.

da8dc06

Order: address code review concerns.

d564a3a

coderabbitai Bot reviewed Feb 13, 2026

View reviewed changes

Comment thread plugins/woocommerce/includes/wc-order-functions.php Outdated

Order: address bots feedback.

4925975

kalessil marked this pull request as draft February 13, 2026 08:29

kalessil added 6 commits February 13, 2026 10:22

Order: rework checkout draft handling.

ded15ce

Order: rework checkout draft handling.

c1f7b8e

Order: actualize new UTs.

2219e6a

Order: workflow polishing.

9cfac13

Order: cleanup.

35c23aa

Order: cleanup.

4d6e676

kalessil marked this pull request as ready for review February 13, 2026 10:09

woocommercebot requested a review from a team February 13, 2026 10:11

Order: fix failing tests.

dc5b4ed

kalessil removed the request for review from a team February 13, 2026 10:20

jorgeatorres self-requested a review February 13, 2026 10:53

Merge branch 'trunk' into performance/63118-reduce-sqls-number-iterat…

76c2c92

…ion-2

kalessil modified the milestones: 10.6.0, 10.7.0 Feb 17, 2026

jorgeatorres approved these changes Feb 17, 2026

View reviewed changes

Merge branch 'trunk' into performance/63118-reduce-sqls-number-iterat…

3d17061

…ion-2

kalessil merged commit 8360c2a into trunk Feb 18, 2026
50 checks passed

kalessil deleted the performance/63118-reduce-sqls-number-iteration-2 branch February 18, 2026 07:24

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[Performance] Orders: reduce the number of SQL queries required to persist a draft order during checkout#63258

[Performance] Orders: reduce the number of SQL queries required to persist a draft order during checkout#63258
kalessil merged 26 commits intotrunkfrom
performance/63118-reduce-sqls-number-iteration-2

kalessil commented Feb 12, 2026 •

edited

Loading

Uh oh!

Uh oh!

Uh oh!

github-actions Bot commented Feb 12, 2026 •

edited

Loading

Uh oh!

coderabbitai Bot commented Feb 12, 2026 •

edited

Loading

Walkthrough

Changes

Estimated code review effort

Uh oh!

github-actions Bot commented Feb 12, 2026 •

edited

Loading

Uh oh!

Uh oh!

coderabbitai Bot left a comment

Uh oh!

Uh oh!

jorgeatorres left a comment

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Conversation

kalessil commented Feb 12, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Submission Review Guidelines:

Changes proposed in this Pull Request:

How to test the changes in this Pull Request:

Uh oh!

Uh oh!

Uh oh!

github-actions Bot commented Feb 12, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Testing Guidelines

Uh oh!

coderabbitai Bot commented Feb 12, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Walkthrough

Changes

Estimated code review effort

Uh oh!

github-actions Bot commented Feb 12, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Test using WordPress Playground

Uh oh!

Uh oh!

coderabbitai Bot left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

jorgeatorres left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

kalessil commented Feb 12, 2026 •

edited

Loading

github-actions Bot commented Feb 12, 2026 •

edited

Loading

coderabbitai Bot commented Feb 12, 2026 •

edited

Loading

github-actions Bot commented Feb 12, 2026 •

edited

Loading