[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

Start at Asset::getSrcset() and reproduce the issue with the listed sizes while watching the debug panel's database-query count. Trace how each transform size is loaded, then verify that all transforms are loaded in a single query and that adding sizes no longer adds queries per size.