Excess database queries for each post

[Enuriru]

For some strange reason Pods runs a separate database queries for each post in addition to the global.

1. Global query with table storage join to get all the data;
2. Select from wp_posts for each post;
3. Select from wp_postmeta for each post;
4. Select from table storage for each post;
5. Select from table storage for each post AGAIN.

That means we have a big slow down: 400 unnecessary queries for just 100 items displayed! Pods gets all the data with one query, why there are these extras?

Tested with meta and table storage with all other plugins disabled. Tested with versions 2.6.11, 2.7.0-b-2, 2.7.0-rc-1. Query Monitor plugin is used to watch the queries.

Pods configuration is pretty simple:

Code: [pods name='test_table']{@post_title}<br>[/pods] or $pods = pods(‘test_table’); while ($pods->fetch()) { echo $pods->display('post_title'); }

The stack traces for 4 and 5 selects are different:

For the meta storage behavior is the same (but there is no table storage queries of course):

Dev tasklist

• [ ] Pods::field() -- Better pass along objects to prevent additional queries from get_permalink() / get_term_link() / get_comment_link() by setting up object from $this->row data like $p = new WP_Post( $this->row )
• [ ] PodsData::fetch() -- Review to determine if unnecessary queries to get_post() can be prevented if we know the select was not optimized

[sc0ttkclark]

Love your detective work here, thanks for laying every single thing out. I'll get these resolved ASAP and/or get them caching better.

[Enuriru]

Found where it is. fetch function in classes/PodsData.php

I replaced $this->row = get_post( $id, ARRAY_A ); on line 1803 to if (!$this->row) $this->row = get_post( $id, ARRAY_A ); and got rid of additional wp_posts queries and one of the table storage queries. Next post type conditionals (taxonomies and etc) need similar fixes I think. Not sure it is a right fix, I tested it on live site and all looks fine but it needs tests of course.

[Enuriru]

And what about the second table storage query, moving $get_table_data = true; from line 1826 to my condition fixes it.

So now it's if (!$this->row) { $this->row = get_post( $id, ARRAY_A ); $get_table_data = true; } on line 1803.

[sc0ttkclark]

I'll review those suggested changes, there's some complexities here as well when dealing with custom SELECT queries in Pods find(), so will have to make sure we account for that as well.

[Enuriru]

Thanks Scott, glad to help with optimizations in such a great framework!

In addition, just figured out that$pods->display('permalink') or {@permalink} magic tag runs ANOTHER ONE wp_posts query in get_permalink.

I'd suggest to replace $value = get_permalink($this->id()); in classes/Pods.php line 759 to $_post = (object) $this->row; $post = new WP_Post($_post); $post->filter = 'raw'; $value = get_permalink($post); This code creates _WPPost object from already existing data so _get_postpermalink called by _getpermalink doesn't need to request it by id from database. Taxonomy, user and comment links have to be fixed someway like this.

[sc0ttkclark]

Wow you're on fire, keep letting us know here as you find other areas and we'll get them all cleaned up.

[Enuriru]

So my website have been working for a month with these changes and all seems ok. Did you review my changes?

[sc0ttkclark]

Looking over this again for 2.8

[sc0ttkclark]

I've summarized the tasklist for the changes needed here, both appear to need further thought as they require the ability to know whether an optimized select was used which would prevent proper post/etc objects from being able to be set up and referenced.