[5.x]: Asset::getSrcset takes too many database queries

[MoritzLost]

What happened?

Description

I'm investigating some performance issues with the MySQL server, so I'm trying to reduce the number of database queries required for a page load, especially on a cold cache, because that's where most of the problems occur.

I found that the single largest source for the query count is Asset::getSrcset(). This uses a separate database query for every single size. In fact, I found that every single size takes two database queries. With 5 transforms in two formats (WebP and AVIF), we get 20 database queries for a single image! And that's just for the srcset, there are additional queries for the base transform.

Steps to reproduce

1. Where image is an Asset, use image.getSrcset([240, 360, 480, 600, 720, 840, 960]) and watch the number of database queries in the debug panel as the number of sizes is increased.

Expected behavior

Asset::getSrcset() should group its queries to avoid the N+1 (or rather, N+2) problem here. All transforms should be loaded in a single query, adding more sizes should not result in additional database queries for each one.

Actual behavior

The number of database queries grows exponentially at an alarming rate with the number of images on a page, and transforms per image.

Craft CMS version

5.8.6

PHP version

8.3

Operating system and version

No response

Database type and version

No response

Image driver and version

No response

Installed plugins and versions

[linear[bot]]

CMS-1494 [5.x]: Asset::getSrcset takes too many database queries

[splendidrob]

@MoritzLost Does eager loading the transforms not help with this? https://craftcms.com/docs/5.x/development/eager-loading.html#eager-loading-image-transforms

[MoritzLost]

@splendidrob In practice, I found it almost impossible to eager-load the transforms I need. That's because all the heavy lifting in terms of sizes, aspect ratios, formats, etc is done by a utility Twig template. So I would have to basically replicate all that legwork in the template that performs the asset query, which is difficult and fickle.

I'm fine with having a couple additional queries if I can avoid all that extra work, because I know that Craft core has the same problem.

But with getSrcset() specifically, I don't see a reason why Craft shouldn't be able to bring down the 20 queries required for a srcset with 10 images down to a single query. All the information is there, it's just a matter of optimizing the code flow to use a single query or doing the eager-loading under the hood. The Craft API should not be a performance trap.

---

As a sidenote, the docs on eager-loading transforms are somewhat sparse. I couldn't find a single example for eager-loading a srcset, for example …

[i-just]

Hi Moritz,

As mentioned by @splendidrob, you can keep the number of queries the same, regardless of how many srcset params you provide, if you eager-load the transforms. For example, when using the <int>w notation, you could go with:

{% set myImage = craft.assets.id(165).withTransforms([{width: '240'}, '360w', '480w']).one() %}

and then you can retrieve the srcset URLs with:

{{ myImage.getSrcset([240, 360, 480]) }}

Once the image transform indexes are created, it would result in the same number of queries regardless of whether you use 3 or 10 srcset values (providing they match what was passed into withTransforms).

You can then still use a named or template-defined transform to get the base url for the image, and depending on your needs and circumstances, you can eager-load that too. For example, if you want to use a template-defined transform, you could go with:

{% set myTransform = {
    mode: 'crop',
    width: 1000,
    quality: 75,
    position: 'top-center'
} %}
{% set myImage = craft.assets.id(165).withTransforms([{width: 1000}, '240w', '360w', '480w']).one() %}
{{ myImage.getUrl(myTransform) }}
{{ myImage.getSrcset([240, 360, 480]) }}

In terms of the docs, there’s a note at the bottom of this section that mentions that the srcset can be eager-loaded. That said, I’ll mention that it could benefit from an example.

I hope this helps!

[MoritzLost]

@i-just Thanks for the reply, Iwona!

As I mentioned above, eager-loading is difficult if you have a utility template or macro to do the heavy lifting in terms of defining transforms. By the time the template has put together all the values of the transform (taking into account stuff like the aspect ratio we want for this particular component, supported image formats, etc), the image has already been loaded. And we can't just move this around, because we need the image dimensions to determine the transforms in the first place.

Is it possible to use eager-loading for transforms for an image that has already been loaded? To at least bring down the number of queries for the srcset? Maybe using Elements::eagerLoadElements()?

---

I don't quite understand your code example. You're eager-loading a transform with {width: 1000}, but then getting the URL for a transform that also includes a custom mode, quality and position. The eager-loaded transform can't be used in this case, can it?

---

I still think that Craft could do more here to help keep the number of queries low. Using eager-loading is an option, however, it requires some difficult trade-offs regarding template structure and separation of concerns as mentioned above.

One simple improvement would be to implement eager-loading or some sort of query batching in getSrcset(). I'm still seeing that every size passed to that methods requires two additional database queries, and that's with all the corresponding rows in the transform index already present. If I pass in 10 sizes, the number of DB queries increases by 20. In this function, Craft knows all about which sizes it needs. Is there anything that would prevent it from getting all the transform indexes required for the srcset in a single query?

Something like lazy eager-loading for transforms would be useful as well.