Une ligne de code, 201 requêtes SQL

D'après une histoire vraie. Le domaine, les noms et le code ont été simplifiés ; les mécanismes, eux, viennent du terrain. Aucune base de données n'a été blessée durant l'écriture de cet article. En production, si.

Il est 9 h 47, un mardi. Dehors, le soleil brille et les oiseaux chantent. Sur le deuxième écran, le dashboard hurle : orange, puis rouge. Le canal support déborde de diagnostics d'une précision chirurgicale : "c'est lent", "toujours lent", "très lent". Les temps de réponse s'envolent, les timeouts tombent en cascade, et la base de données rampe pour sa survie. Personne n'a déployé depuis une semaine. La cause tient en une ligne de code, écrite quatorze mois plus tôt, relue en code review, approuvée, et parfaitement raisonnable.

Rembobinons.

L'histoire

Un chaud jour d'été, quinze mois avant le dashboard rouge. Nouvelle équipe, nouveau projet, backlog vierge : ce moment de la vie d'un logiciel où tout le monde s'aime encore. Une API de gestion de contrats, .NET, EF Core. Vous configurez le DbContext, la doc mentionne une option confortable, et franchement, elle vend du rêve :

builder.Services.AddDbContext<ContractsContext>(options =>
    options
        .UseSqlServer(connectionString)
        .UseLazyLoadingProxies());

public class Contract
{
    public int Id { get; set; }
    public string Reference { get; set; } = "";
    public DateTime CreatedAt { get; set; }
    public virtual ICollection<Document> Documents { get; set; } = [];
}

Une ligne dans le Program.cs, un mot-clé virtual, et les navigations se chargent toutes seules au moment où on les touche. Plus jamais de navigation à null, plus jamais de Include oublié. À ce moment précis de l'histoire, c'est un choix défendable : personne dans cette équipe n'est incompétent, et c'est exactement ce qui rend la suite intéressante. Retenez juste cette option. Elle reviendra.

L'écran "derniers contrats" arrive dans le sprint. L'endpoint est écrit en dix minutes, et il est propre : requête bornée, DTO de sortie, async partout. La liste affiche la référence de chaque contrat et le nombre de documents qui lui sont attachés.

// Simplifié pour l'article. Le mécanisme, lui, est identique.
app.MapGet("/contracts", async (ContractsContext db) =>
{
    var contracts = await db.Contracts
        .OrderByDescending(c => c.CreatedAt)
        .Take(200)
        .ToListAsync();
 
    return contracts.Select(c => new ContractSummary(
        c.Id,
        c.Reference,
        c.Documents.Count,
        c.CreatedAt)).ToList();
});

La code review dit LGTM (Looks Good To Me), et elle a raison : il n'y a rien à signaler. En dev, la base contient 12 contrats et l'endpoint répond en 8 ms. Personne n'ouvre le log SQL d'un endpoint qui répond en 8 ms. Il faudrait pourtant. Le voici :

SELECT TOP(200) [c].[Id], [c].[Reference], ... FROM [Contracts] AS [c] ORDER BY ...
SELECT [d].[Id], [d].[Name], [d].[Size], ... FROM [Documents] AS [d] WHERE [d].[ContractId] = @__p_0  -- contrat 1
SELECT [d].[Id], [d].[Name], [d].[Size], ... FROM [Documents] AS [d] WHERE [d].[ContractId] = @__p_0  -- contrat 2
SELECT [d].[Id], [d].[Name], [d].[Size], ... FROM [Documents] AS [d] WHERE [d].[ContractId] = @__p_0  -- contrat 3
-- ... et neuf de plus

Chaque c.Documents.Count traverse un proxy qui charge la collection entière : pour afficher "34 documents", EF matérialise les 34 lignes, toutes leurs colonnes comprises, puis les compte en mémoire. Une requête pour la liste, une par contrat.

Retenez la forme plutôt que le nombre : le Take(200) fixe le plafond à 201. Avec les 12 contrats de la dev, on en est à 13, à 0,5 ms pièce sur un SQL Server local : ça ne se voit pas. La bombe est posée. Elle tique en silence.

La base a grandi : 3 000 contrats, de vrais clients, de vrais avenants, et des documents qui s'accumulent sur chaque contrat, parce que c'est ce que font les contrats. L'endpoint répond maintenant en 300 ms : assez lent pour agacer le PO, pas assez pour ouvrir une enquête. Quelqu'un prononce la phrase magique qui clôt toutes les conversations de performance : "c'est EF, c'est normal."

On pose un cache de trente secondes par utilisateur "en attendant" (le provisoire étant le matériau le plus durable de l'informatique), ce qui suffit à ramener la courbe dans le vert sans rien régler, et un ticket Investiguer lenteurs /contracts descend dans le backlog avec la flottabilité d'une enclume.

Notez le détail qui compte : le code n'a pas changé d'une ligne depuis le sprint 3. Seules les données ont grandi. Le coût du lazy loading n'est pas indexé sur votre code, il est indexé sur votre production, et c'est pour ça que personne ne le voit venir. Le compteur, lui, est déjà à son plafond : 201, et il y restera. Ce qui grimpe maintenant, c'est le volume de lignes matérialisées et la concurrence. Le N+1 passe de "trop de requêtes" à "trop de chaos" : la bombe du sprint 3 ne tique plus, elle chauffe.

Un grand compte signe (le grand compte tient toujours ce rôle dans les post-mortems, c'est une convention du genre). Il arrive avec 40 000 contrats, mais ce n'est pas leur nombre qui compte : ce sont les documents, des dizaines par contrat désormais, et le trafic, qui fait ouvrir l'écran en continu.

L'arithmétique devient brutale : toujours 201 requêtes par affichage, mais elles matérialisent maintenant des dizaines de milliers de lignes pour afficher... des nombres. Et comme ces allers-retours sont séquentiels, la requête garde sa connexion du premier au dernier : l'écran met plusieurs secondes là où il en mettait 300.

C'est ce qui tue le pool (100 connexions par défaut). Un affichage n'y prend pas 201 connexions d'un coup : il en prend une, et la garde 201 fois trop longtemps. Comme un client qui commanderait 201 cafés un par un sans quitter le comptoir : une seule caisse mobilisée, mais à cent clients pareils, la file ne bouge plus.

System.InvalidOperationException: Timeout expired. The timeout period elapsed
prior to obtaining a connection from the pool.

Et voici le détail cruel : les endpoints voisins tombent aussi. Eux n'ont rien demandé. Ils attendent juste une connexion qu'une boucle innocente garde le temps de 201 allers-retours. Le N+1 n'affame pas que son appelant ; il affame l'immeuble entier.

Il est 9 h 47, un mardi. Dehors, les oiseaux chantent toujours. Le dashboard est rouge. La ligne du sprint 3 n'a pas changé. Le monde autour d'elle, si.

L'autopsie

La suite est moins intéressante : une cellule de crise, le cache du sprint 17 rallongé dans l'urgence, et le réflexe du rollback, vite abandonné faute de déploiement à annuler. L'incident est clos, les utilisateurs ont retrouvé leurs contrats. Penchons-nous sur le corps.

Quand vous activez UseLazyLoadingProxies(), EF Core ne modifie pas vos entités : il en fabrique des sous-classes à la volée, via Castle DynamicProxy, la bibliothèque qui fabrique aussi vos mocks de tests. Votre entité de production partage sa technologie avec des objets payés pour faire semblant. D'où le virtual obligatoire : le proxy surcharge vos navigations pour en intercepter l'accès, et à chaque accès, il déroule ce petit algorithme :

// Ce que le proxy génère, en substance.
public override ICollection<Document> Documents
{
    get
    {
        if (!_loaded)                 // pas encore chargée ?
        {
            _lazyLoader.Load(this);   // alors : une requête SQL, ici
            _loaded = true;
        }
        return base.Documents;        // sinon : déjà en mémoire, rien à payer
    }
}

Relisez lentement : il y a une requête SQL dans un getter. L'opération la plus coûteuse de votre système, habillée de la syntaxe la moins chère du langage. La signature ne prévient pas, le type ne prévient pas, l'IDE ne prévient pas. Le compilateur, lui, trouve ça très bien.

Conséquence : le coût d'une requête ne dépend plus de la requête, mais du code qui se promène ensuite dans le graphe d'objets. Et ce code n'est pas toujours le vôtre : un sérialiseur JSON, un mapper consciencieux, un logger qui interpole, et chacun déclenche sa rafale de requêtes sans le savoir. Si le DbContext est déjà mort à ce moment-là, l'accès jette une exception, de préférence en pleine sérialisation de la réponse.

Voilà pourquoi personne n'a rien vu dans notre histoire : en dev, le coût était minuscule, et en code review, c.Documents.Count se lit comme un accès de propriété, parce que c'en est un, syntaxiquement. Le lazy loading a honoré son contrat à la lettre, en silence, 201 fois par affichage.

Une dernière pièce pour le dossier. EF6 activait le lazy loading par défaut ; EF Core, construit par la même équipe, l'a relégué dans un package séparé (Microsoft.EntityFrameworkCore.Proxies), désactivé par défaut, entouré d'avertissements dans la doc. Personne ne vous interdit rien : l'option existe, elle est documentée, elle fonctionne. Elle a simplement été rangée tout en haut du placard, là où on met les produits d'entretien quand les enfants commencent à marcher. Affaire classée.

Le sevrage

Un mot d'abord : personne ne lit un article de performance au sprint 1. Vous arrivez ici au sprint 30, et couper le lazy loading d'un coup est la fausse bonne idée du lundi matin : vous échangeriez un problème de lenteur contre un problème de données manquantes. Sevrage progressif, donc.

Inutile de chercher les coupables avec grep : un lazy load ressemble trait pour trait à un accès de propriété. Il faut mesurer. Un APM (Application Performance Monitoring : Datadog, Application Insights) si vous en avez un ; sinon, EF Core adore avouer, et un DbCommandInterceptor qui compte les requêtes par endpoint s'écrit en une après-midi. Le podium sera sans suspense : un ou deux écrans concentrent l'essentiel des dégâts. Commencez par le pire, mesurez, recommencez. Maintenant, l'arsenal.

Include / ThenInclude

L'évidence : demander à EF de charger les documents en même temps que les contrats, en une seule requête.

var contracts = await db.Contracts
    .Include(c => c.Documents)
    .OrderByDescending(c => c.CreatedAt)
    .Take(200)
    .ToListAsync();

201 requêtes deviennent 1, le progrès est réel. Mais regardez ce qu'elle rapporte : tous les documents de tous les contrats, pour afficher des comptes. Include répond à la question "combien ?" en livrant le carton entier. Et dès qu'on inclut plusieurs collections, le JOIN répète chaque contrat une fois par combinaison d'enfants : c'est l'explosion cartésienne, et AsSplitQuery() est le correctif qu'EF fournit pour ça. Mieux que le lazy loading, donc, mais on paie encore pour des données qu'on jette.

La projection vers un DTO

La vraie réponse en lecture : ne pas charger des entités, demander un résultat.

var contracts = await db.Contracts
    .OrderByDescending(c => c.CreatedAt)
    .Take(200)
    .Select(c => new ContractSummary(
        c.Id,
        c.Reference,
        c.Documents.Count,   // devient un COUNT(*) en SQL
        c.CreatedAt))
    .ToListAsync();

C'est la ligne du titre, au caractère près. Déplacée à l'intérieur du Select, donc à l'intérieur de la requête, elle cesse d'être un piège : EF la traduit en COUNT(*) et la base renvoie 200 lignes déjà comptées. Une requête, zéro document matérialisé, et le lazy loading devient hors sujet : il n'y a plus d'entité sur laquelle mentir. On demande un nombre, on reçoit un nombre ; le concept est d'une audace folle.

Ce réflexe de projeter toutes les lectures vers des DTO mène tout droit au CQRS. Personne ne m'avait prévenu non plus. Article pour un autre jour.

Le chargement explicite

Pour les rares cas où il faut vraiment la collection d'un seul contrat, une règle métier, un écran de détail :

await db.Entry(contract)
    .Collection(c => c.Documents)
    .LoadAsync();

Même requête que celle du proxy, mais à visage découvert : elle apparaît dans le code, dans la code review et dans les résultats de grep. C'est le lazy loading qui a arrêté de mentir. Le coût redevient une décision qu'on lit, pas un effet de bord qu'on subit.

Que ça ne revienne jamais

Réparer l'écran, c'est la moitié du travail. L'autre moitié, c'est de faire en sorte que la prochaine boucle innocente ne survive pas jusqu'à la production. Trois garde-fous, du plus doux au plus mécanique :

• La lucidité. Savoir ce que coûte un lazy load et à quoi il ressemble. Bonne nouvelle : vous venez de terminer cette étape.
• Le défaut. Sur un projet neuf, ne référencez simplement pas Microsoft.EntityFrameworkCore.Proxies. On n'a jamais eu à désactiver ce qu'on n'a pas activé.
• L'exception immédiate. En dev et en test, transformez chaque lazy load en erreur franche : la bombe explose au moment où on la pose, ce qui simplifie considérablement le déminage.

La dernière tient en une option :

options.ConfigureWarnings(w => w.Throw(CoreEventId.NavigationLazyLoading));

Activée en dev et en test, elle fait du premier lazy load venu un test qui échoue, avec la stack trace qui pointe la ligne fautive. Une régression N+1 devient un build rouge un jeudi après-midi. Pas un dashboard rouge un mardi à 9 h 47.

Ce qu'il faut retenir