All CMS-driven websites rely on one form of a database or another. This database contains almost all of the content and the configuration of the site. For some sites, almost everything except the aesthetics of the site is stored in the database.
A database differs from regular, boring file storage in that it stores data in a structured, organised manner. This organisation means that one piece of information can relate to another and be organised, and then searched, in a structural manner – not unlike browsing the shelves in a store. These search requests are known as queries. You can do a lot of cool stuff with a simple database, and a smartly designed DB will carry a site a long way.
The downside of this is that going through this library of information is time consuming and demanding – even for a speedy, modern web hosting server, like those that we recommend for our clients. The core issue is that the CMS doesn’t automatically know what it needs to grab out of the database, and has to make repeat trips to collect everything that’s needed. That’s not very efficient – it’s like going to the store once for every individual ingredient. If we read the entire recipe before we start, we can pick up a few things at the same time.
Luckily, Craft CMS has the tools to make reading from a database much quicker. It’s known as Eager Loading, and we’ll dive into some examples right away.
Firstly, the basics:
A fairly simple example, this displays all 15 entries in a list. The title and some details of the ‘image’ field are also displayed. It looks like this:
Not the most glamorous look, but this is a typical use of Craft – this could easily be adapted to a list of blog posts, a main menu or any number of things – these types of database queries make up the backbone of a site:
This is one of the most simple ways to use an entry query. It’ll return any entry on the site, in any section. Most of the time you’ll see a section parameter passed through to narrow down what’s displayed to a specific type of entry, but that isn’t super relevant for this example – what we care about is on the frontend of the site, in the yii admin panel.
Craft includes this debugging tool – it’s really useful digging into the nuts & bolts of a site’s templates. Today’s all about the database information over on the right-hand side. Because this is running on my local development server (a laptop doing a bunch of other stuff) the “Time” is going to be hard for us to keep track of. The Memory and DB queries count are much easier to monitor.
Memory usage of 16mb for such a simple template is fairly OK – this could be improved, however it’s no reason for concern right now. The DB count is what we’re looking at – there’s 48 individual queries happening for a list of only 15 entries. That’s a little high, and will only go up as the site grows.
What’s happening is that each entry is being loaded and as it’s processed, a new query is being sent out to grab the image data. Eager loading can be added as simply as this:
That was easy. Let’s take a quick look at the DB queries now:
Simply adding the “with” parameter enables eager loading. We can use this method to collect any fields associated with an entry, including related entries, categories and assets – all things that can be quite slow for the site to fetch.
Reducing the DB queries by 50% means a faster load time, lower server resources used and overall a better user experience – even on this tiny test site.
A more complex example
What if we need to display more than one set of entries on a page? What if we’d like to eager-load more than one field. Simple answer – add what you’d like to load to an array, and pass that to the query:
On an upcoming site we’re developing, this technique reduced the total page queries from over 180 to around 75. The page’s load speed also jumped up from around 3 seconds to just under one.
This sort of speed increase translates directly into more traffic and conversions for the site. It will also improve performance and reliability of the site, as the CMS will be under less stress having to dig through the database to find what it’s after. Any set of entries we query on the site can likely have one or more field eager-loaded.
Adding this sort of optimisation is quick and painless, so all of our Craft CMS sites use it wherever we can.
