perf(admin-setting): improve query performance

[mqudsi]

WP-Give has a really bad query plan for the admin backend that includes a separate query per row of results (instead of a join) and duplicate queries without caching.

There is no quick fix for this, it needs to be completely architectured to first obtain all relevant information via a few SQL queries that aren't along the lines of

foreach ($donations as $donation) {
//SQL query here, WHERE id = $donation->id
}

and aren't

foreach ($posts as $post) {
//SELECT * FROM posts WHERE id = $post->id
}

Simply loading /wp-admin/edit.php?post_type=give_forms&page=give-payment-history executes 259 queries, many of which are the same query with the same parameters.

Just going to /wp-admin/edit.php?post_type=give_forums&page=give-settings results in duplicate queries selecting all columns from wp_posts for all posts with virtually no limit (LIMIT 0, 999) repeated 50 times:

Also, by default WP-Give installs a dashboard plugin that is active simply visiting /wp-admin/ and causes these same queries to be executed. I had to comment out that code to make the dashboard usable.

[DevinWalker]

Hey @mqudsi - We've been working hard to improve our queries - see #1932 #1944 #2121

I agree there are some queries, like that long monstrosity you're showing within Recurring, need resolving. However, the one you mention about the dashboard widget should now be resolved, I believe.

What version of Give were you using when you were looking into this?

[mqudsi]

This hasn't been fixed as of 2.0.6.

Here's what simply logging in to /wp-admin/ looks like:

I'm sorry, but that is insane.

update_meta_cache() ran this query 344 times:

SELECT payment_id, meta_key, meta_value
FROM wp_give_paymentmeta
WHERE payment_id IN (13783)
ORDER BY meta_id ASC

and this query 614 times:

SELECT meta_id
FROM wp_give_paymentmeta
WHERE meta_key = '_thumbnail_id'
AND payment_id = 13783

as a result of 345 calls to update_meta_cache().

The callstack:

wp_dashboard()
wp-admin/includes/dashboard.php:221
do_meta_boxes()
wp-admin/includes/template.php:1063
wp_dashboard_quick_press()
wp-admin/includes/dashboard.php:481
get_default_post_to_edit()
wp-admin/includes/post.php:608
wp_insert_post()
wp-includes/post.php:3581
do_action('save_post')
wp-includes/plugin.php:453
auto_featured_image()
wp-content/themes/Education/functions.php:457
set_post_thumbnail()
wp-includes/post.php:6139
update_post_meta()
wp-includes/post.php:1804

[mqudsi]

Additionally, get_option for give_completed_upgrades is called 3 times

1:

do_action('admin_init')
wp-includes/plugin.php:453
Give_Updates->__change_donations_label()
wp-content/plugins/give/includes/admin/upgrades/class-give-updates.php:212
Give_Updates->get_total_update_count()
wp-content/plugins/give/includes/admin/upgrades/class-give-updates.php:1016
Give_Updates->get_pending_db_update_count()
wp-content/plugins/give/includes/admin/upgrades/class-give-updates.php:1031
Give_Updates->get_updates()
wp-content/plugins/give/includes/admin/upgrades/class-give-updates.php:979
give_get_completed_upgrades()
wp-content/plugins/give/includes/misc-functions.php:1252
get_option()
wp-includes/option.php:98

2:

do_action('admin_menu')
wp-includes/plugin.php:453
Give_Updates->__register_menu()
wp-content/plugins/give/includes/admin/upgrades/class-give-updates.php:252
Give_Updates->get_total_update_count()
wp-content/plugins/give/includes/admin/upgrades/class-give-updates.php:1016
Give_Updates->get_pending_db_update_count()
wp-content/plugins/give/includes/admin/upgrades/class-give-updates.php:1031
Give_Updates->get_updates()
wp-content/plugins/give/includes/admin/upgrades/class-give-updates.php:979
give_get_completed_upgrades()
wp-content/plugins/give/includes/misc-functions.php:1252
get_option()
wp-includes/option.php:98

3:

do_action('admin_notices')
wp-includes/plugin.php:453
Give_Updates->__show_notice()
wp-content/plugins/give/includes/admin/upgrades/class-give-updates.php:652
Give_Updates->get_total_new_db_update_count()
wp-content/plugins/give/includes/admin/upgrades/class-give-updates.php:1059
Give_Updates->get_pending_db_update_count()
wp-content/plugins/give/includes/admin/upgrades/class-give-updates.php:1031
Give_Updates->get_updates()
wp-content/plugins/give/includes/admin/upgrades/class-give-updates.php:979
give_get_completed_upgrades()
wp-content/plugins/give/includes/misc-functions.php:1252
get_option()
wp-includes/option.php:98

[DevinWalker]

Hey @mqudsi - I agree, loading the dashboard right now with many donations is very slow. That's why in 2.1 we've optimized this substantially. It's going to be released this upcoming week so please re-review your queries load time and number of queries post update.

@ravinderk is also looking into your additional optimization feedback and reducing the number of queries to only the number of objects is our big priority right now. We appreciate your additional feedback and code references.

[ravinderk]

@mqudsi I am looking into your suggestion and I will update you soon.

[mqudsi]

@ravinderk @DevinWalker it's not an issue with the number of donations (which isn't that high). I didn't mean "a query like this is being run a million times" but rather "this exact query is being run a million times," as in, there's a bug and the same query is being run to update the metadata for the same donation.

(unless the query monitor plugin is using a sample of the query run and reporting it for all instances of the query. I don't know.)

[ravinderk]

@DevinWalker Closing this issue because last year we worked on admin loading performance and it is fine now. I will create a seperate issue for recurring admin loading performance.