eZ Community » Forums » Developer » Best practices to reduce number of...
expandshrink

Best practices to reduce number of database queries ?

Best practices to reduce number of database queries ?

Thursday 05 March 2015 8:16:56 pm - 1 reply

Hi all,

I'm working on a eZ Publish 5 website, and I have a problem about how to use subrequests and how to fetch content of a content class from any displayed page.

I used to try understand things from the eZ5 documentation, some tutorials ( like this page : http://partialcontent.com/code/working-with-ez-publish-5-subrequests ) and posts from this forum.

My problem is that on a generated page from any type of content, I need to load contents from other content classes (to create the menu, the footer, the typical list of last 5 entries from the blog, …).

For this, I use subrequests from the twig template, like : {{ render( controller( "nameofmycontroller:nameofmyaction" ) ) }}

In the controller, I will load stuff (using locationService or contentService or searchService depending on my needs) to make my request and render a twig template to display the results.

The main problem with this is that on every page, I reach a HUGE amount of database queries (from 100 to 200) on every page.

I haven't found how to fix it yet… What is the best practise when fetching content to optimise performance ?

Modified on Thursday 05 March 2015 8:41:46 pm by Trent Trent

Sunday 15 March 2015 3:40:13 pm

Hello Trent.

I see this particular topic as one with two sides.

A, you want to make sure that requests don't use more queries than they need by optimizing the code and architecture within reason. B, you want to make sure those requests are cached correctly, either by the internal reverse proxy, or by an external one, such as Varnish.

In addition, Persistence Cache should take care of preventing queries from harming the system too much. It gets updated when content is modified. It can be further optimized by storing it on memcached or Reddis, but by default, it just does its job without you caring too much.

In order achieve a (don't run more queries than required), it mostly boils down to how you structure your page and use sub-requests. All common elements that don't belong to the displayed content should use sub-request, with properly chosen cache keys. For instance, the menu should vary depending on what's highlighted/deployed, depending on its UX, and so on.

This way, you ensure that you don't generate those parts more than necessary. As long as they're cached, they will be returned without any repository query. Netgen has published an article about Caching with the new stack that should help you.

Does this help ?

expandshrink

You must be logged in to post messages in this topic!

36 542 Users on board!

Forums menu

Proudly Developed with from