Lots of database queries

[Sti3bas]

Can you reproduce this issue on default Wordpress theme (eg Storefront)?

Yes.

Can you reproduce this issue when all other plugins are disabled except WooCommerce, Polylang and Hyyan WooCommerce Polylang Integration?

Yes.

What product versions and settings are you using when this issue occurs?

• PHP: 7.3.6
• WordPress: 5.2.3
• WooCommerce: 3.7.0
• Polylang: 2.6.4
• Hyyan WooCommerce Polylang Integration: 1.4.3
• Browser: Chrome 77.0.3865.75

Steps to Reproduce

1. Add shipping zone

What I Expected

Lower number of queries

What Happened Instead

+20 queries for each new shipping zone

I have a site with lots of shipping zones and after upgrading WC to the latest version number of queries increased by ~1268 (was 436, now 1704).

I've tried to debug the problem and I found that commenting this line reduces queries to 446 : https://github.com/hyyan/woo-poly-integration/blob/master/src/Hyyan/WPI/Gateways.php#L79

Seems like it always returns the new instance of WC_Payment_Gateways instead of returning a singleton.

I've also tried to downgrade WC version and I've found that the number of queries significantly reduces when I get back to 3.3.5.

WordPress Environment

` ### WordPress Environment ###

WordPress address (URL): http://woo.test
Site address (URL): http://woo.test
WC Version: 3.7.0
REST API Version: ✔ 1.0.2
Log Directory Writable: ✔
WP Version: 5.2.3
WP Multisite: –
WP Memory Limit: 2 GB
WP Debug Mode: –
WP Cron: ✔
Language: en_US
External object cache: –

Server Environment

Server Info: nginx/1.15.6
PHP Version: 7.3.6
PHP Post Max Size: 256 MB
PHP Time Limit: 30
PHP Max Input Vars: 1000
cURL Version: 7.65.1
OpenSSL/1.0.2s

SUHOSIN Installed: –
MySQL Version: 5.5.5-10.3.10-MariaDB
Max Upload Size: 256 MB
Default Timezone is UTC: ✔
fsockopen/cURL: ✔
SoapClient: ✔
DOMDocument: ✔
GZip: ✔
Multibyte String: ✔
Remote Post: ✔
Remote Get: ✔

Database

WC Database Version: 3.7.0
WC Database Prefix: wp_
MaxMind GeoIP Database: ❌ The MaxMind GeoIP Database does not exist - Geolocation will not function. You can download and install it manually from https://dev.maxmind.com/geoip/geoip2/geolite2/ to the path: . Scroll down to "Downloads" and download the "MaxMind DB binary
gzipped" file next to "GeoLite2 Country". Please remember to uncompress GeoLite2-Country_xxxxxxxx.tar.gz and upload the GeoLite2-Country.mmdb file only.

Total Database Size: 4.38MB
Database Data Size: 2.94MB
Database Index Size: 1.44MB
wp_woocommerce_sessions: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_woocommerce_api_keys: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_woocommerce_attribute_taxonomies: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_woocommerce_downloadable_product_permissions: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wp_woocommerce_order_items: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_woocommerce_order_itemmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_woocommerce_tax_rates: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wp_woocommerce_tax_rate_locations: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_woocommerce_shipping_zones: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_woocommerce_shipping_zone_locations: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_woocommerce_shipping_zone_methods: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_woocommerce_payment_tokens: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_woocommerce_payment_tokenmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_woocommerce_log: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_commentmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_comments: Data: 0.02MB + Index: 0.09MB + Engine InnoDB
wp_links: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_options: Data: 2.02MB + Index: 0.05MB + Engine InnoDB
wp_pakkelabel_shipping_diffrentiated: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_podsrel: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wp_postmeta: Data: 0.14MB + Index: 0.19MB + Engine InnoDB
wp_posts: Data: 0.06MB + Index: 0.06MB + Engine InnoDB
wp_termmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_terms: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_term_relationships: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_term_taxonomy: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_usermeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_users: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wp_wc_admin_notes: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_wc_admin_note_actions: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_wc_customer_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_wc_download_log: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_wc_order_coupon_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_wc_order_product_lookup: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wp_wc_order_stats: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wp_wc_order_tax_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_wc_product_meta_lookup: Data: 0.02MB + Index: 0.09MB + Engine InnoDB
wp_wc_tax_rate_classes: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_wc_webhooks: Data: 0.02MB + Index: 0.02MB + Engine InnoDB

Security

Secure connection (HTTPS): ❌
Your store is not using HTTPS. Learn more about HTTPS and SSL Certificates.
Hide errors from visitors: ✔

Active Plugins (4)

Polylang: by WP SYNTEX – 2.6.4
Query Monitor: by John Blackbourn – 3.3.7
Hyyan WooCommerce Polylang Integration: by Hyyan Abo Fakher – 1.4.3 – Not tested with the active version of WooCommerce
WooCommerce: by Automattic – 3.7.0

Dropin Plugins (1)

db.php: Query Monitor Database Class

Settings

API Enabled: –
Force SSL: –
Currency: DKK (DKK)
Currency Position: left_space
Thousand Separator: .
Decimal Separator: ,
Number of Decimals: 2
Taxonomies: Product Types: external (external)
grouped (grouped)
simple (simple)
subscription (subscription)
variable (variable)
variable subscription (variable-subscription)

Taxonomies: Product Visibility: exclude-from-catalog (exclude-from-catalog)
exclude-from-search (exclude-from-search)
featured (featured)
outofstock (outofstock)
rated-1 (rated-1)
rated-2 (rated-2)
rated-3 (rated-3)
rated-4 (rated-4)
rated-5 (rated-5)

Connected to WooCommerce.com: –

WC Pages

Shop base: #6 - /shop/
Cart: #7 - /cart/
Checkout: #8 - /checkout/
My account: #9 - /my-account/
Terms and conditions: ❌ Page not set

Theme

Name: Storefront
Version: 2.5.1 – 2.5.3 is available
Author URL: https://woocommerce.com/
Child Theme: ❌ – If you are modifying WooCommerce on a parent theme that you did not build personally we recommend using a child theme. See: How to create a child theme
WooCommerce Support: ✔

Templates

Overrides: –

Action Scheduler

Complete: 8
Oldest: 2019-06-26 06:49:24 +0000
Newest: 2019-08-16 09:11:07 +0000

Pending: 0
Oldest: –
Newest: –

Canceled: 2
Oldest: 2019-06-26 06:50:24 +0000
Newest: 2019-06-26 07:49:24 +0000

In-progress: 0
Oldest: –
Newest: –

Failed: 0
Oldest: –
Newest: –

`

[Jon007]

How many shipping zones do you have? 63 according to your stats..
And how many payment gateways? - from active plugins it looks like only a few provided with wooCommerce.
What type of page are you measuring these stats on? Admin? Product page? Checkout?

WC_Payment_Gateways::instance(); is the correct code for singleton use of the gateways, this access is used within the WooCommerce source code itself.

What is the link between gateways and shipping zones?
I see no link within WooCommerce native functionality and no relevant recent changes in the gateways area in either Woocommerce or this plugin.

Do you have some sort of code customisation which is checking gateways for each shipping zone?

[Sti3bas]

The above Wordpress environment is from the test site and as I said it adds +20 queries for each new shipping zone (without any shipping methods).

How many shipping zones do you have? 63 according to your stats..

36 shipping zones with ~3 shipping methods for each on production site.
3 shipping zones without shipping methods on test site.

And how many payment gateways? - from active plugins it looks like only a few provided with wooCommerce.

3 active payment gateways on production site.
1 active payment gateway (Cash on delivery) on test site.

What type of page are you measuring these stats on? Admin? Product page? Checkout?

It happens on all pages. Here is the screenshot from the production site homepage:

WC_Payment_Gateways::instance(); is the correct code for singleton use of the gateways, this access is used within the WooCommerce source code itself.

Yes, but this line generates all these additional queries after you update to WC 3.3.6 or later with the same version (latest) of woo-poly.

What is the link between gateways and shipping zones?

I guess none as all gateways are available in all shipping zones (no restrictions).

Do you have some sort of code customisation which is checking gateways for each shipping zone?

No. As I said I've tested it on fresh Wordpress installation only polylang and woo-poly plugins enabled as you can see in WordPress Environment section above.

[Jon007]

Idea initialise the related woopoly component only on this filter which happens in the load process of the WC_Payment_Gateways singleton:
$load_gateways = apply_filters( 'woocommerce_payment_gateways', $load_gateways );
Often gateways are loaded on every page as required by payment plugins that implement Buy Now type functionality so this plugin always loads it's gateway function with new Gateways(); which also always loads the actual gateways.
instead the gateway class loader
add_action('wp_loaded', array($this, 'loadOnWpLoaded'));
could be modified to use the woocommerce_payment_gateways filter

(that probably won't quite work as the woopoly function needs to run after the gateways are loaded not before. maybe an alternative like woocommerce_available_payment_gateways)