[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

Asset::getSrcset() から始め、デバッグパネルのデータベースクエリ数を確認しながら、記載されたサイズで問題を再現します。各 transform size がどのように読み込まれるかを追跡し、その後、すべての transform が単一のクエリで読み込まれること、およびサイズを追加してもサイズごとにクエリが追加されなくなっていることを確認します。