mesh
mesh copied to clipboard
linchpin
Weigh storing get_mesh_sections() in transient to speed up larger posts
If you have a lot of sections within a page/post/custom post type, the query count can become quite large. We've seen some sites that have large pages turn into having 200+ queries in a single view.
This is less of an issue if you have persistent object caching and full page caching built into your environment. But I am wondering if it makes sense to build a utility method to store the entire mesh_sections query and subsequent child mesh_sections (aka blocks/columns) in one diesel blob in the DB. Mesh sections aren't paginated, and always have at least 1 block. So at minimum to get a section you are adding 2 additional queries to a page (and realistically you are adding 10) just based on a simple 1 section 1 block layout.
This implementation could be extended even further to store the post_meta for each section/block and would dramatically cut down on the queries in a given page if no persistent object cache is available.
Mainly looking for pros and cons here. The challenge is that all get_mesh_sections() and get_mesh_section_blocks() are separated out into different methods at the moment so we can't simply get/set_transient and see immediate performance benefits. There has to be an intermediate method build to store the information.
Loop through all sections in the post, then loop through all blocks/columns in a section. Store all the data into 1 serialized object as a transient stored for X amount of time (filterable of course) but maybe default it to an hour. The amount of queries is now cut down a ton on a larger page.
Thoughts on storing the transient a lot longer than an hour? Like 24 hours or more, and clearing the transient on update?
I'm ok with it being more than an hour for sure as long as it's filterable.
I played around with transients and wasn't able to come up with a solution for the mesh_get_sections and mesh_get_section_blocks functions.
I did add a transient for storing the output of mesh_display_sections though. This reduced the number of queries down from 133 to just 29. I tested on a page that had 26 Mesh sections.
I tried to do something similar for the admin post edit screen, but ran into problems with TinyMCE not working correctly.
The transient is set for 24 hours (filterable), and is deleted if the post is edited or deleted.
I'm going to keep working on a way to improve this further, but it's a start.
Not sure if any work has been done on this recently, but I took a rough stab at a slightly different approach than using transients alone.
https://github.com/linchpinagency/mesh/pull/62
Basically, the idea is to store all of the section and block IDs as metadata on the page when saving/updating. This allows us to run one query to get all of the sections and corresponding blocks for an object and store it in static cache. I think the current method of retrieval is to generate one query to get sections and then, for each section, run another query to get it's blocks. 😱
As a simple test: I created a page with 3 Mesh sections (4 column, 3 column & 2 column). Prior to optimization when loading the page within the admin, query count was 147. After optimization, query count was reduced to 135; that's an 8% difference. Pretty cool.
I didn't spend a whole lot of analyzing, but post-optimization, runtime of mesh_get_sections() may be slightly worse now that we've thrown a foreach loop in there. Ultimately, I think it ends up being a tradeoff: less queries for more CPU cycles at runtime, but if any of you guys are really good at calculating Big-O notation and want to get into the weeds trying to figure that out, then by all means go for it (this is where my brain usually starts to explode). 😜
