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

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

Inizia da Asset::getSrcset() e riproduci il problema con le dimensioni elencate, osservando il conteggio delle query al database nel pannello di debug. Traccia come viene caricata ogni dimensione di trasformazione, quindi verifica che tutte le trasformazioni vengano caricate con una sola query e che l’aggiunta di dimensioni non aggiunga più query per ogni dimensione.