Pods: 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 behaviour is the same (but there is no table storage queries of course):

[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 _WP_Post_ object from _already existing data_ so _get_post_permalink_ called by _get_permalink_ 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?