[Resolved] Views within a view within a view loads too slow

[Author]

Posts

[Rita]

Hi there

I am hoping you help with some advice...

I need to display a list of "the cheapest ticket - for each event - for each day".
The site is setup with various relationships and there are multiple conditions to filter events, tickets and venues posts.
The list is for a google sheets import and the problem is each time the google sheet runs a refresh import it times out because the list generating on a designated page on the website loads way too slowly - absolutely due to the need for multiple views within views.
I am trying to find another way to get the data...

Here is how it currently works:

RELATIONSHIPS
Tickets (woo products) are children of both Events and Venues.

PAGE
hidden link
No header, footer, title, feature image or crumb. No follow. Public. No cache.
Calls: In the body field is the CONTENT TEMPLATE shortcode.

CONTENT TEMPLATE
Calls: VIEW1
Note that inside that VIEW1 are VIEW2, VIEW3 and VIEW4.

VIEW 1
Purpose: Get all relevant 'events'
Find: all 'event' posts that have 'ticket' children - I am using a filter in functions.php to do this. See image attached.
Filter: 'ff post status' is a string equal to 1
Sort: By 'post title' ascending.
Limit: x
Calls: In the loop is VIEW2.

(( - Right now I have this view limit set to 20 otherwise the site will throw a 503 timeout - see attached image for server PHP settings - ))

VIEW 2
Purpose: Get all related and relevant 'tickets'
Find: all 'ticket'
Filter: By tickets in the 'events-tickets' relationship related to the 'event' post in the loop
Filter: 'ticket expire' is a number greater than NOW()
Filter: '_stock_status' is a string equal to 1
Filter: 'ff post status' is a string equal to 1
Sort: By 'ticket start date' ascending
Limit: x
Calls: In the loop results are grouped by 'ticket start date' - I am using a [gtxdateheading] shortcode for this. See image attached.
Calls: Inside each date group are various custom fields, VIEW3 and VIEW4.

VIEW3
Purpose: To count how many individual tickets in each date group.
Find: All 'tickets'
Filter: 'ticket start' is a number equal to VIEW_PARAM(date)
Filter: 'ff parent id' is a string equal to VIEW_PARAM(eventid)
Filter: '_stock_status' is a string equal to 1
Filter: 'ff post status' is a string equal to 1
Sort: By 'post date' descending
Limit: x
Calls: In the loop is a custom select field 'ticket bundle' prefixed with '+'. See the [calculate] shortcode in VIEW2.
Calls: I am using a 'calculate' shortcode to calculate the sum of all the raw values for each ticket.

VIEW4
Purpose: To find the cheapest ticket in each date group.
Find: All 'tickets'
Filter: 'ticket start' is a number equal to VIEW_PARAM(date)
Filter: 'ff parent id' is a string equal to VIEW_PARAM(eventid)
Filter: '_stock_status' is a string equal to 1
Filter: 'ff post status' is a string equal to 1
Sort: By 'ticket each' ascending
Limit: 1
Calls: In the loop are various fields from the ticket, the parent event and the parent venue.

--

That's it in a nutshell. This setup gives me the exact list I need.

The final list of tickets displayed is just under 500... But it loads too slow...

Do you think there is another way?

I have tried many MANY ideas to try to remove the need for so many nested views but none suffice...

Any ideas are MOST certainly welcome!

Regards

Rita

[Luo Yang]

Hello,

You are using nested views to query database, it will produce lots of WP_Query and conduct performance problem, I suggest you follow our document to optimize your views:
https://toolset.com/documentation/legacy-features/views-plugin/how-to-use-views-parametric-search-on-large-sites/

[Rita]

Hi Luo

Thanks for the link. I have considered the cache. In this case the cache on every view described above will be disabled automatically because, as per the list via your link, one or more of the following apply:

• The View shortcode has any attribute other than name, id or target_id, since the results of this View will be modified, thus preventing the user from viewing the cached output.

• The View is nested or has a filter or an option that depends on the current page, current user, or current archive data.

• The content being loaded is almost always changed - new posts, edited posts etc

I have also set the website caching to off because I need the view to deliver the most up-to-date result...

I was hoping you might know of another way to get the same result...

Rita

[Luo Yang]

In your case, I don't think there is other better workaround, you might need to consider increase the webserver limitations, for example:
https://wordpress.org/support/article/editing-wp-config-php/#increasing-memory-allocated-to-php

[Rita]

Hi Luo

Ahhh... I was hoping you would tell me I had done it all wrong and there is a much better way! 😉

Yes, I had considered php performance...
In my initial post I attached the webserver php performance for this installation.
I have already increased it to:

memory_limit = 512M
max_execution_time = 300
max_input_time = 60
post_max_size = 256M
upload_max_filesize = 100M

I am not an expert on this and have set these tentatively.
I am not certain what is optimum and what is 'too much'....
I do understand that if I increase it too much it can actually hinder performance.
Can you recommend better settings?

[Luo Yang]

Here are my suggestions.

1) Increase PHP memory limitations, for example:
memory_limit = 1024M

2) Remove this step, and test again:
Find: all 'event' posts that have 'ticket' children - I am using a filter in functions.php to do this.
You can try to display the parent post in the child post view.

[Rita]

Thanks for your advice.

I increased the php memory limitations to the below amounts. But to no avail. I still get a 504 server timeout.
1. memory_limit = 1024M
2. max_input_time = 90M

The reason why the main ticket view (view 2) is nested in a parent event view is because the final list of tickets needs to be:
1. the cheapest ticket in the group + how many tickets in the group (see view 3 and 4 above)
2. for each group of tickets grouped by date (see view 2 above)
3. for each parent event (see view 1 above)

I could remove the event view (view 1) if there was a way to double group ticket results:
1. First by the parent event id
2. and then for each parent event id, by date, using a custom date field

But I don't think this is possible no?

[Luo Yang]

This step you mentioned above:

Find: all 'event' posts that have 'ticket' children - I am using a filter in functions.php to do this.

You are using custom codes to do the conditional checking, it will produce a lots of database query and conduct performance problem, you can simply try to remove these codes you mentioned in your screenshot:
https://toolset.com/wp-content/uploads/2020/12/1883725-functions.php_filter_only_events_with_children_tickets.png

And test again.

And as I mentioned above
https://toolset.com/forums/topic/views-within-a-view-within-a-view-loads-too-slow/#post-1886477
In the child ticket post view, within shortcode [wpv-items-found] ... [/wpv-items-found]:
https://toolset.com/documentation/programmer-reference/views/views-shortcodes/#wpv-items-found
you can display the parent event post information by following our document,
https://toolset.com/documentation/programmer-reference/views/views-shortcodes/item-attribute/

Hope it is clear.

[Rita]

RE: "all 'event' posts that have 'ticket' children - I am using a filter in functions.php to do this"
Understand. I have already removed that from the view.

RE: "In the child ticket post view, within shortcode [wpv-items-found] ... [/wpv-items-found] you can display the parent event post information by following our document"
Understand. I thought I already had that as good as I could get it...
In VIEW 2 I just call the 2 nested views; VIEW 3 & 4
Then in VIEW 4 I display all the information - parent event, parent venue, ticket info.

VIEW 2 is grouping the tickets by a custom date field...

Even so, I have moved the ticket parent event ID, Title and URL shortcodes to the VIEW 2 but all the other fields getting information about the cheapest ticket need to stay in VIEW 4 as this information is not specific yet in the 'grouped by date' VIEW 2.

Thought!? Would the group "all tickets related to the parent event in this loop by date custom field" function in VIEW 2 be slowing it down? I refer to this code I have in functions.php that creates a shortcode to group view results by a custom date field: https://toolset.com/wp-content/uploads/2020/12/1883725-functions.php_group_results_by_custom_field_date.png

[Rita]

The good news is that by removing the view filter in functions.php to only display events that have children AND by moving 3 of the custom fields from nested VIEW 4 to nested VIEW 2 still loads the page verrrrrry slow but it loads. It doesn't timeout (504).

Its an improvement. But it still doesn't solve the overall issue...

I anticipate that the site is only going to get bigger; more events, many more tickets...

[Luo Yang]

As I mentioned in my first answer, nested view will conducts performance problem:
https://toolset.com/forums/topic/views-within-a-view-within-a-view-loads-too-slow/#post-1884689

I suggest try to remove the nested view, for example:
1) In the problem page you mentioned above:
hidden link

Display only parent "Event" view,

2) In the parent "Event" view, add pagination setting, in view's loop, display single event post link

3) In the single event post, display related child "ticket" posts.

[Rita]

Thanks Luo
I have tried that too.
My conclusion is that there is no way around it...
Appreciate you taking the time to have a look at this with me.