[Resolved] Slow queries on nested relationships views

[Author]

Posts

[dmytroP]

So I have this setup:

I've created a custom post type. Then I've put it into a many-to-many relationship with Woocommerce products. And the CPT post displays related products using Views. And that works fine.

But then I want the product view to display what other CPT posts the products are related to.

And I end up in this nested views setup where a CPT (custom post type) post displays the view with a product loop, and then each product displays a list of CPT posts related to the products.

Here's an example of such a CPT post: hidden link

The problem is that this nested view generates slow queries, as you can see in the screenshot. And for each product, one slow query is generated. So on CPT posts with a long list of products, there may be 50+ slow queries.

I understand it's a pretty complex and heavy setup, and I wouldn't complain if I hadn't an almost exact same setup, but with a 2019 Toolset Views version 2.7.2 on a different site I no longer own. It also displays a view inside of a view, but it has no slow queries problem at all. Everything loads fast regardless of how many products there are.

What I tried (and it didn't help):
- changing server config to max out the capacity (innodb_buffer_pool_size, memory_limit, etc.)
- disabling all other plugins, including the caching plugin

What kinda helped but didn't solve the problem:
Caching works, but it's a temporary measure. Also, some pages are really huge and, due to slow queries, take too much time to load and be cached. The server returns a 500 error.

How do I solve this slow queries problem without installing the 2019 version of the toolset views?

[dmytroP]

Noticed an interesting thing:
When I delete the view that is nested but leave the reference to it, Toolset still generates one single slow query. But only one.

[Nigel Supporter Languages: English (English ) Spanish (Español ) Timezone: Europe/London (GMT+01:00)]

Hi Dmitriy

Can I get you to clarify the post types and relationships involved here to better understand exactly what you are doing?

What are the post types, how are they connected, and then how you are outputting them?

Are you using the legacy Views editor (shortcode based) or the current block editor for your Views? I'm not sure what "when I delete the view that is nested but leave the reference to it" means.

It may not be the version of Views that accounts for the difference with the site you built in 2019. Types is responsible for post types and post relationships, and back in January 2019 (when Views 2.7.2 was released) Types was on version 3.2.4.

The later update to Types 3.4 included a change to the relationship database table structures that introduced an extra layer of complexity to better integrate with WPML and handle multilingual related content, and it is conceivable that that change may have resulted in some slower queries.

But I'd like to see more details of your setup to understand exactly what you are aiming to achieve to comment further.

[dmytroP]

> Can I get you to clarify the post types and relationships involved here to better understand exactly what you are doing?

As I've said, I created a normal custom post type using the Post Types menu in the Toolset. Let's call it Profiles.
Then I installed a Woocommerce plugin that created Products.
I connected the Profiles and the Products with the many-to-many relationship.

Then, I created the views using the old editor with shortcodes (Loop Editor). That allowed me to just copy the "code" from the old website that I mentioned. I made it years ago and I need a similar or even the same setup today.

So I created the views for displaying Products related to the Profiles.

And inside those Products views, I output another view that displays a list of Profiles related to the Products.

After that, I created a content template for the Profiles where I output the Product views that contain that other view with related profiles to the current product in the loop.

So, in the end, it's a Content Template that displays Views of Products related to the Profile. And each Product contains a view that displays a list of Profiles related to the current product in the loop.

> I'm not sure what "when I delete the view that is nested but leave the reference to it" means.

Since I use the older Loop Editor version I can put the [wpv-view name="name"] shortcode to display the view.
But when I actually delete this view, the reference remains.

> It may not be the version of Views that accounts for the difference with the site you built in 2019.
It's definitely not the same because I used Toolset Views back then and now it's Toolset Blocks. I understand it's different, but is it really an improvement if it works slower now?

> But I'd like to see more details of your setup to understand exactly what you are aiming to achieve to comment further.
I want to be able to display the nested views without having slow queries. It would be a shame if needed to download the old version of the plugin for that.

[Waqar]

Thank you for sharing these background details.

Your setup sounds straightforward, but, I'm sure you'll understand that more data and more level of nesting involved, will make the queries more complex.

If you could share the temporary admin login details of the website, along with the example product page, we can see how the involved views are set up. And suggest some possible performance improvements accordingly.

Note: Your next reply will be private and making a complete backup copy is recommended before sharing the access details.

[Waqar]

Thank you for sharing the access details.

I've checked the content template 'Content template for Influencers' used by the example page.

That template uses 33 unique views and all of them load the "Also used by" view (when it is in the published state).

In my tests with the "Also used by" view restored, I do see an increased loading time (about 40-45 seconds in total), for the first load. However, after the first loading, the subsequent loadings are complete in under 8 seconds.
( It must be because of the cache getting kicked in )

Looking inside the loop content template of view 'Also used by', it is loading only the post link through the shortcode:
[wpv-post-link class="alsousedlink"]

But it is loading that single line through a loop item content template 'Loop item in Also used by' through the shortcode:
[wpv-post-body view_template="loop-item-in-also-used-by"]

From a performance perspective, it is a good idea to load the simple loop item content such as this, directly inside the '<wpv-loop> ... </wpv-loop>' tags, instead of involving a content template.
( screenshot: hidden link )

Whenever the use of a loop item content template is unavoidable, because of long/complex content, it is beneficial to include the suppress_filters="true" attribute to the 'wpv-post-body' shortcode:
( ref: https://toolset.com/documentation/programmer-reference/views/views-shortcodes/#wpv-post-body )

[wpv-post-body view_template="loop-item-in-also-used-by" suppress_filters="true"]

This ensures that extra third-party content filters are not processed on that content.

You can update all the involved views based on these suggestions and they should help with the performance. In simpler setups, the effect can be unnoticeable. But when these many views are involved, measures like these can make a significant impact.

[dmytroP]

Thanks, I tried moving the template shortcode inside the <wpv-loop> tag and adding the suppress filters parameter. It didn't change much.

> However, after the first loading, the subsequent loadings are complete in under 8 seconds. ( It must be because of the cache getting kicked in)

You're right, but as far as I understand, that cache lives for 24 hours (I've read that somewhere in your docs). And I've got 16k pages like that one. Caching 16k pages with load times of up to a minute daily will load the server too much. That is not the solution. I need to get rid of the slow queries altogether.

Slow queries appear even on pages with just one such nested view (example: hidden link). The problem is in the wrong setup or in the query itself.

How come the older version of the plugin can do this effortlessly and the new one can't? Something is definitely not right here.

Please help me solve this issue.

P.S. Are these messages private? Because you share sensitive info in your messages (e.g. screenshot name) that I would like you to not share publicly.

[dmytroP]

Anyone?

[Waqar]

Thank you for waiting, while I performed some further testing and research over the weekend.

In summary, there is no safe, consistent and reliable way to substitute or avoid the relationship queries, in the existing set up.

As Nigel mentioned earlier, the current relationship structure was introduced in Types 3.0, in May 2018. It uses relational dedicated tables to store information about the connected posts, which is a proven and industry wide standard model.

Prior to that, the relationship information was stored as the custom field values, which had its own challenges and shortcomings. The switch was also crucial for better compatibility with the WPML plugin, that is used for multilingual websites.

Since the development of new features for Toolset has been paused ( ref: https://toolset.com/2022/06/whats-next-for-toolset/ ), I'm afraid, it is very unlikely that an even more efficient relationship structure gets introduced, anytime soon.

Would you be open to the idea of using a custom taxonomy, to replace the post-relationship? For example, you can register a new custom taxonomy 'Groups' and attach it to the both post types which are currently joined through post relationship.

Next, you can join similar posts in both these post types through the same 'Group' terms and then the view "Also used by", will be able to filter the posts, by the taxonomy query and not by post-relationship query. The taxonomy query offers better performance, compared to the post-relationship and even the custom field (meta) query.