Spike: Slow response time on subscriptions order screen

[joshuatf]

Performance Details

On larger sites, the subscriptions orders page in the admin is among the discovered slower (>2s) requests. See p2-pcShBQ-2J3 for more details.

Request: /wp-admin/admin.php?page=wc-orders--shop_subscription
Average Time: 13.59s

Affected Sites

This appears to affect sites that use subscriptions (either from WooPayments or WooCommerce Subscriptions) and most likely those that have more orders and/or more subscriptions.

The sample size for sites with this subscriptions performance issue is relatively small, but the issue does appear to happen in the 10,000 - 100,000 completed orders range.

Suspected Causes

It's very likely that this is related to the main orders count query performance issues, but this should be confirmed.

This should be tested with a site with many subscriptions and subscription orders. The specific VIP sites may need to be cloned to diagnose the exact cause and ensure all slow queries are caught.

Context

• Admin orders performance discussion
• Slow count queries issue

[joshuatf]

cc @Automattic/quark

[james-allan]

Hey @joshuatf, thanks for all the work you've put into this project so far.

I've just returned from sabbatical so forgive my ignorance -- is there a process for getting access to a test site? I have a large-ish subscriptions store for testing purposes with 13 K subscriptions and roughly 5000 orders.

At the moment it takes less than 1 second on average to load the /wp-admin/admin.php?page=wc-orders--shop_subscription page.

Based on the numbers being discussed in the order count query post, I'm guessing you got these slow response times on a much, much larger site.

I'm going to try ramp up my test site to larger numbers in the meantime but if you have sites we can test on or more information about numbers, that would be great.

[james-allan]

I've spent a little bit of time ramping up the number of orders on my site for testing purposes. It's slow going at the moment, but as I do that I wanted to get a sense of the queries running and where the time is being spent running what queries.

It's very likely that this is related to the main orders count query performance issues, but this should be confirmed.

This is true, the Subscriptions admin list table has the exact same query which runs on the orders list table. We'll need to follow along and see how WC core handles it to see if there's anything we will need to do specifically for Subscriptions.

While I was testing on a relatively small site, the query which populates the Subscriptions list table results is actually slower compared to the order equivalent. I'm not sure why that is just yet because the only difference between the order and subscription queries is just the order type shop_order shop_subscription and the statuses in the query.

SELECT wp_wc_orders.id
FROM wp_wc_orders
WHERE 1=1
AND (wp_wc_orders.status IN ('wc-pending','wc-processing','wc-on-hold','wc-completed','wc-cancelled','wc-refunded','wc-failed'))
AND (wp_wc_orders.type = 'shop_order')
GROUP BY wp_wc_orders.id
ORDER BY wp_wc_orders.date_created_gmt DESC
LIMIT 0, 20

SELECT wp_wc_orders.id
FROM wp_wc_orders
WHERE 1=1 
AND (wp_wc_orders.status IN ('wc-pending','wc-active','wc-on-hold','wc-cancelled','wc-switched','wc-expired','wc-pending-cancel'))
AND (wp_wc_orders.type = 'shop_subscription')
GROUP BY wp_wc_orders.id
ORDER BY wp_wc_orders.date_created_gmt DESC
LIMIT 0, 20

Once I get to testing on a much larger site, I'll see if this is just a coincidence or if there's something to this.