The client in question was using some archaic cataloguing software, which in turn sent data to Catsy, their PIM, which then communicated with the website. On top of that, their 3,000+ product catalogue had every product variant as its own individual entity. Recommendations were made, but for a number of business reasons, none of this could be changed.

What this meant in execution was some very creative templating logic, complicated querying (with an unfortunate number of n+1 loops) and eventually caching, built and customized for every template.

Due to the complex logic involved in associating products with each other; with their asset matrixes, image loops, and globals, such as the ability to customize table columns for different categories, the load on the database quickly escelated.

Given budget constraints, I wanted to avoid creating a custom API integration or plugin. Everything here was going to be Craft’s (generally stellar) API importing tool Feed Me and stock functionality.

I leaned heavily on the built-in Yii debugging toolbar, keeping a close eye on database and PHP execution times with every change I made. Eager loading significantly increased performance in troublesome areas, and was added everywhere it would be a benefit.

Caching, too, was added on a piece-meal basis. Navigation, though globally consistent at first glance, needed to be keyed to specific entries to properly match active states. Parts of the homepage needed to be excluded entirely, along with other pages that changed often and weren’t too taxing. Caching keys had to be slotted in across the product display pages and half-dozen+ listing templates. A common pattern:

{% set cacheId = entry is defined ? entry.id : 'non-entry' %}
{% cache globally using key 'menu-' ~ cacheId for 3 months if getenv('CRAFT_ENVIRONMENT') == 'production' %}
  {# Cacheable content #}
{% endcache %}

Ultimately, I was able to cut load times by as much as 400% on the most taxing templates. No need for Blitz, no need to upgrade server resources.