Partager via

Interrogation efficace

  • Article

Interroger efficacement est un vaste sujet, qui couvre des aspects aussi variés que les index, les stratégies de chargement d’entités associées, etc. Cette section détaille certains thèmes courants pour accélérer vos requêtes ainsi que les pièges généralement rencontrés par les utilisateurs.

Utiliser correctement les index

Le principal facteur qui détermine la rapidité d’exécution d’une requête varie selon que celle-ci utilise correctement ou non les index, le cas échéant : les bases de données sont généralement utilisées pour stocker de grandes quantités de données, et les requêtes qui parcourent des tables entières sont généralement à l’origine de graves problèmes de performances. Les problèmes d’indexation ne sont pas faciles à identifier, car il n’est pas immédiatement évident de savoir si une requête donnée doit utiliser un index ou non. Par exemple :

// Matches on start, so uses an index (on SQL Server)
var posts1 = context.Posts.Where(p => p.Title.StartsWith("A")).ToList();
// Matches on end, so does not use the index
var posts2 = context.Posts.Where(p => p.Title.EndsWith("A")).ToList();

Un bon moyen de détecter les problèmes d’indexation consiste à identifier d’abord une requête lente, puis à examiner son plan de requête via votre outil favori de base de données. Pour plus d’informations sur la procédure à suivre, consultez la page relative au diagnostic des performances. Le plan de requête indique si la requête parcours l’intégralité de la table, ou si elle utilise un index.

En règle générale, vous n’avez pas besoin de connaissances particulières relatives à EF pour l’utilisation des index ou le diagnostic des problèmes de performances associés. Des connaissances générales relatives aux bases de données et aux index sont tout aussi pertinentes pour les applications EF que pour celles qui n’utilisent pas EF. Voici une liste de quelques recommandations générales à garder à l’esprit quand vous utilisez des index :

  • Bien que les index accélèrent les requêtes, ils ralentissent également les mises à jour, car ils doivent eux-mêmes être actualisés. Évitez de définir des index qui ne sont pas nécessaires, et utilisez des filtres d’index pour limiter l’index à un sous-ensemble de lignes, et réduire ainsi cette surcharge.
  • Les index composites peuvent accélérer les requêtes qui filtrent sur plusieurs colonnes, mais ils peuvent également accélérer les requêtes qui ne filtrent pas sur toutes les colonnes de l’index, en fonction de l’ordre. Par exemple, un index sur les colonnes A et B accélère les requêtes qui effectuent un filtrage en fonction de A et B ainsi que les requêtes qui effectuent un filtrage uniquement en fonction de A. Toutefois, il n’accélère pas les requêtes qui effectuent un filtrage uniquement en fonction de B.
  • Si une requête effectue un filtrage en fonction d’une expression sur une colonne (par exemple price / 2), vous ne pouvez pas utiliser d’index simple. Toutefois, vous pouvez définir une colonne persistante stockée pour votre expression, et créer un index sur celle-ci. Certaines bases de données prennent également en charge les index d’expression, qui peuvent être directement utilisés pour accélérer le filtrage des requêtes en fonction d’une expression.
  • Différentes bases de données permettent de configurer les index de diverses manières. Dans de nombreux cas, les fournisseurs EF Core les exposent via l’API Fluent. Par exemple, le fournisseur SQL Server vous permet de configurer un index cluster, ou de définir son facteur de remplissage. Consultez la documentation de votre fournisseur pour plus d’informations.

Sélectionner uniquement les propriétés nécessaires

EF Core facilite l’interrogation des instances d’entité, puis l’utilisation de ces instances dans le code. Toutefois, l’interrogation d’instances d’entité peut fréquemment extraire plus de données que nécessaire de votre base de données. Prenez en compte les éléments suivants :

foreach (var blog in context.Blogs)
{
    Console.WriteLine("Blog: " + blog.Url);
}

Bien que ce code ait uniquement besoin de la propriété Url de chaque Blog, l’entité Blog entière est extraite, et les colonnes inutiles sont transférées à partir de la base de données :

SELECT [b].[BlogId], [b].[CreationDate], [b].[Name], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]

Vous pouvez optimiser cela à l’aide de Select pour indiquer à EF les colonnes à sélectionner :

foreach (var blogName in context.Blogs.Select(b => b.Url))
{
    Console.WriteLine("Blog: " + blogName);
}

Le code SQL résultant extrait uniquement les colonnes nécessaires :

SELECT [b].[Url]
FROM [Blogs] AS [b]

Si vous devez sélectionner plusieurs colonnes, effectuez l’opération en créant un type anonyme C# avec les propriétés de votre choix.

Notez que cette technique est très utile pour les requêtes en lecture seule, mais que les choses se compliquent si vous devez mettre à jour les blogs extraits, car le suivi des changements d’EF fonctionne uniquement avec les instances d’entité. Vous pouvez effectuer des mises à jour sans charger d’entités entières en attachant une instance de Blog modifiée, et en indiquant à EF quelles sont les propriétés qui ont changé. Toutefois, il s’agit d’une technique plus avancée qui n’en vaut peut-être pas la peine.

Limiter la taille du jeu de résultats

Par défaut, une requête retourne toutes les lignes qui correspondent à ses filtres :

var blogsAll = context.Posts
    .Where(p => p.Title.StartsWith("A"))
    .ToList();

Dans la mesure où le nombre de lignes retournées dépend des données réelles de votre base de données, il est impossible de connaître la quantité de données chargées à partir de la base de données, la quantité de mémoire utilisée par les résultats et la charge supplémentaire générée au moment du traitement de ces résultats (par exemple, en les envoyant au navigateur d’un utilisateur via le réseau). Plus important encore, les bases de données de test contiennent souvent peu de données. Ainsi, tout fonctionne bien durant les tests, mais des problèmes de performances apparaissent soudainement quand la requête commence à s’exécuter sur des données réelles et que de nombreuses lignes sont retournées.

Il est donc généralement préférable de limiter le nombre de résultats :

var blogs25 = context.Posts
    .Where(p => p.Title.StartsWith("A"))
    .Take(25)
    .ToList();

Au minimum, votre IU peut afficher un message indiquant qu’il existe davantage de lignes dans la base de données (et permettre leur récupération d’une autre manière). Une solution complète implémente la pagination, où votre IU affiche uniquement un certain nombre de lignes à la fois, et permet aux utilisateurs de passer à la page suivante selon les besoins. Pour plus d’informations sur l’implémentation efficace de cette opération, consultez la section suivante.

Pagination efficace

La pagination fait référence à la récupération des résultats sous forme de pages, plutôt qu’en une seule fois. Cette opération est généralement effectuée pour les jeux de résultats volumineux, où une interface utilisateur s’affiche, et permet à l’utilisateur de naviguer vers la page de résultats suivante ou précédente. L’utilisation des opérateurs Skip et Take (OFFSET et LIMIT en SQL) est une technique courante pour implémenter la pagination dans des bases de données. Bien qu’il s’agisse d’une implémentation intuitive, elle est assez inefficace. Pour une pagination qui permet de se déplacer page par page (par opposition à un déplacement vers des pages arbitraires), utilisez à la place la pagination par jeu de clés.

Pour plus d’informations, consultez la documentation relative à la pagination.

Dans les bases de données relationnelles, toutes les entités associées sont chargées via l’introduction d’opérations JOIN dans une seule requête.

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url], [p].[PostId], [p].[AuthorId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [Blogs] AS [b]
LEFT JOIN [Post] AS [p] ON [b].[BlogId] = [p].[BlogId]
ORDER BY [b].[BlogId], [p].[PostId]

Si un blog classique comporte plusieurs billets associés, les lignes de ces billets vont dupliquer les informations du blog. Cette duplication conduit au problème de l’« explosion cartésienne ». Plus le nombre de chargements de relations un-à-plusieurs augmente, plus la quantité de données dupliquées peut croître et impacter les performances de votre application.

EF permet d’éviter cette situation via l’utilisation de « requêtes fractionnées », qui chargent les entités associées via des requêtes distinctes. Pour plus d’informations, lisez la documentation sur les requêtes fractionnées et les requêtes uniques.

Remarque

L’implémentation actuelle des requêtes fractionnées implique l’exécution d’un aller-retour pour chaque requête. Nous prévoyons d’améliorer cela à l’avenir, et d’exécuter toutes les requêtes en un seul aller-retour.

Il est recommandé de lire la page dédiée aux entités associées avant de poursuivre la lecture de cette section.

Quand nous avons affaire à des entités associées, nous savons généralement à l’avance ce que nous devons charger. L’exemple classique est celui du chargement d’un certain ensemble de blogs avec tous leurs billets. Dans ces scénarios, il est toujours préférable d’utiliser le chargement hâtif pour permettre à EF d’extraire toutes les données nécessaires en un seul aller-retour. La fonctionnalité qui consiste à utiliser la méthode Include avec un filtre vous permet également de limiter les entités associées à charger, tout en conservant le chargement hâtif, et donc l’exécution en un seul aller-retour :

using (var context = new BloggingContext())
{
    var filteredBlogs = context.Blogs
        .Include(
            blog => blog.Posts
                .Where(post => post.BlogId == 1)
                .OrderByDescending(post => post.Title)
                .Take(5))
        .ToList();
}

Dans d’autres scénarios, nous ne savons pas toujours de quelle entité associée nous avons besoin avant d’obtenir son entité principale. Par exemple, au moment du chargement d’un Blog, nous pouvons être amenés à consulter d’autres sources de données (éventuellement un service web) pour savoir si les billets de ce blog nous intéressent. Dans ce cas, le chargement explicite ou différé peut être utilisé pour extraire séparément les entités associées, et remplir la navigation des billets du blog. Notez que dans la mesure où ces méthodes ne sont pas hâtives, elles nécessitent des allers-retours supplémentaires vers la base de données, ce qui est source de ralentissement. En fonction de votre scénario spécifique, il peut être plus efficace de charger toujours tous les billets au lieu d’exécuter des allers-retours supplémentaires et d’obtenir de manière sélective uniquement les billets dont vous avez besoin.

Attention au chargement différé

Le chargement différé semble souvent être un moyen très utile d’écrire une logique de base de données, car EF Core charge automatiquement les entités associées à partir de la base de données au fur et à mesure que votre code y accède. Cela permet d’éviter de charger des entités associées qui ne sont pas nécessaires (comme dans le cas du chargement explicite), et de libérer à première vue le programmeur de la gestion des entités associées. Toutefois, le chargement différé est particulièrement sujet à la production d’allers-retours supplémentaires inutiles, qui peuvent ralentir l’application.

Prenez en compte les éléments suivants :

foreach (var blog in context.Blogs.ToList())
{
    foreach (var post in blog.Posts)
    {
        Console.WriteLine($"Blog {blog.Url}, Post: {post.Title}");
    }
}

Ce morceau de code en apparence anodin itère au sein de tous les blogs et leurs billets, pour les imprimer. L’activation de la journalisation des instructions d’EF Core révèle les éléments suivants :

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [b].[BlogId], [b].[Rating], [b].[Url]
      FROM [Blogs] AS [b]
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (5ms) [Parameters=[@__p_0='1'], CommandType='Text', CommandTimeout='30']
      SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title]
      FROM [Post] AS [p]
      WHERE [p].[BlogId] = @__p_0
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@__p_0='2'], CommandType='Text', CommandTimeout='30']
      SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title]
      FROM [Post] AS [p]
      WHERE [p].[BlogId] = @__p_0
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@__p_0='3'], CommandType='Text', CommandTimeout='30']
      SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title]
      FROM [Post] AS [p]
      WHERE [p].[BlogId] = @__p_0

... and so on

Comment cela se fait-il ? Pourquoi toutes ces requêtes sont-elles envoyées pour les simples boucles ci-dessus ? Avec le chargement différé, les billets d’un blog sont chargés (de manière différée) uniquement quand vous accédez à sa propriété Posts. Ainsi, chaque itération dans la boucle foreach interne déclenche une requête de base de données supplémentaire, dans son propre aller-retour. Ainsi, une fois que la requête initiale a chargé tous les blogs, nous avons une autre requête par blog, qui charge tous ses billets. Cela s’appelle parfois le problème N+1, et peut entraîner des problèmes de performances très importants.

En supposant que nous ayons besoin de tous les billets des blogs, il est logique d’utiliser ici le chargement hâtif à la place. Nous pouvons utiliser l’opérateur Include pour effectuer le chargement. Toutefois, dans la mesure où nous avons besoin uniquement des URL des blogs, nous devons respecter le principe qui consiste à charger ce qui est nécessaire uniquement. Nous allons donc interroger de manière sélective les champs nécessaires à la place :

foreach (var blog in context.Blogs.Select(b => new { b.Url, b.Posts }).ToList())
{
    foreach (var post in blog.Posts)
    {
        Console.WriteLine($"Blog {blog.Url}, Post: {post.Title}");
    }
}

Ainsi, EF Core récupère tous les blogs ainsi que leurs billets en une seule requête. Dans certains cas, il peut également être utile d’éviter les effets de l’explosion cartésienne à l’aide de requêtes fractionnées.

Avertissement

Dans la mesure où le chargement différé rend extrêmement facile le déclenchement par inadvertance du problème N+1, il est recommandé de l’éviter. Le chargement hâtif ou le chargement explicite indiquent très clairement dans le code source le moment où un aller-retour de base de données se produit.

Mise en mémoire tampon et streaming

La mise en mémoire tampon fait référence au chargement de tous vos résultats de requête en mémoire, alors que le streaming signifie qu’EF remet à l’application un seul résultat à chaque fois, sans jamais que l’ensemble des résultats ne soit contenu en mémoire. En principe, les besoins en mémoire d’une requête de streaming sont fixes. Ils sont les mêmes, que la requête retourne 1 ligne ou 1 000 lignes. En revanche, pour une requête de mise en mémoire tampon, plus le nombre de lignes retournées est élevé, plus la quantité de mémoire nécessaire est importante. Pour les requêtes qui génèrent des jeux de résultats volumineux, cela peut être un facteur de performances important.

Le fait qu’une requête utilise la mise en mémoire tampon ou le streaming dépend de la façon dont elle est évaluée :

// ToList and ToArray cause the entire resultset to be buffered:
var blogsList = context.Posts.Where(p => p.Title.StartsWith("A")).ToList();
var blogsArray = context.Posts.Where(p => p.Title.StartsWith("A")).ToArray();

// Foreach streams, processing one row at a time:
foreach (var blog in context.Posts.Where(p => p.Title.StartsWith("A")))
{
    // ...
}

// AsEnumerable also streams, allowing you to execute LINQ operators on the client-side:
var doubleFilteredBlogs = context.Posts
    .Where(p => p.Title.StartsWith("A")) // Translated to SQL and executed in the database
    .AsEnumerable()
    .Where(p => SomeDotNetMethod(p)); // Executed at the client on all database results

Si vos requêtes retournent seulement quelques résultats, vous n’avez probablement pas à vous en soucier. Toutefois, si votre requête est susceptible de retourner un grand nombre de lignes, il est préférable de privilégier le streaming à la mise en mémoire tampon.

Remarque

Évitez d’utiliser ToList ou ToArray si vous avez l’intention d’utiliser un autre opérateur LINQ sur le résultat. Cela entraîne inutilement une mise en mémoire tampon de tous les résultats. Utilisez AsEnumerable à la place.

Mise en mémoire tampon interne par EF

Dans certaines situations, EF met lui-même en mémoire tampon le jeu de résultats de manière interne, quelle que soit la façon dont vous évaluez votre requête. Voici les deux cas où cela se produit :

  • Quand une stratégie de nouvelles tentatives d’exécution est en place. Cela permet de vérifier si les mêmes résultats sont retournés en cas de nouvelles tentatives d’exécution de la requête.
  • Quand la requête fractionnée est utilisée, les jeux de résultats de toutes les requêtes à l’exception de la dernière sont mis en mémoire tampon, sauf si la fonctionnalité MARS (Multiple Active Result Sets) est activée sur SQL Server. En effet, il est généralement impossible d’avoir plusieurs jeux de résultats de requête actifs en même temps.

Notez que cette mise en mémoire tampon interne se produit en plus des mises en mémoire tampon que vous provoquez via les opérateurs LINQ. Par exemple, si vous utilisez ToList sur une requête et si une stratégie de nouvelles tentatives d’exécution est en place, le jeu de résultats est chargé en mémoire deux fois : une fois de manière interne par EF, et une fois par ToList.

Suivi, absence de suivi et résolution d’identité

Il est recommandé de lire la page dédiée au suivi et à l’absence de suivi avant de poursuivre la lecture de cette section.

EF effectue le suivi des instances d’entité par défaut, pour que les changements apportés à celles-ci soient détectés et rendus persistants quand SaveChanges est appelé. Les requêtes de suivi ont un autre impact : EF détecte si une instance a déjà été chargée pour vos données, et retourne automatiquement cette instance suivie au lieu d’en retourner une nouvelle. Cela s’appelle la résolution d’identité. Du point de vue des performances, le suivi des changements signifie ceci :

  • EF gère de manière interne un dictionnaire des instances suivies. Quand de nouvelles données sont chargées, EF vérifie le dictionnaire afin de déterminer si une instance fait déjà l’objet d’un suivi pour la clé de cette entité (résolution d’identité). La maintenance du dictionnaire et les recherches prennent un certain temps au moment du chargement des résultats de la requête.
  • Avant de remettre une instance chargée à l’application, EF effectue une capture instantanée de cette instance, et la conserve de manière interne. Quand SaveChanges est appelé, l’instance de l’application est comparée à la capture instantanée pour découvrir quels sont les changements à rendre persistants. La capture instantanée utilise plus de mémoire, et le processus de capture instantanée lui-même prend du temps. Il est parfois possible de spécifier un comportement de capture instantanée distinct, éventuellement plus efficace, via des comparateurs de valeurs, ou d’utiliser des proxys de suivi des changements pour contourner complètement le processus de capture instantanée (bien que cela comporte son propre lot d’inconvénients).

Dans les scénarios en lecture seule où les changements ne sont pas enregistrés dans la base de données, les surcharges ci-dessus peuvent être évitées à l’aide de requêtes sans suivi. Toutefois, dans la mesure où les requêtes sans suivi n’effectuent pas de résolution d’identité, une ligne de base de données référencée par plusieurs autres lignes chargées est matérialisée sous la forme d’instances distinctes.

À titre d’exemple, supposons que nous chargions un grand nombre de billets à partir de la base de données ainsi que le blog référencé par chaque billet. Si 100 billets référencent le même Blog, une requête de suivi détecte cette situation via la résolution d’identité. Toutes les instances de Post référencent la même instance de Blog dédupliquée. En revanche, une requête sans suivi duplique le même Blog 100 fois, et le code de l’application doit être écrit de manière à en tenir compte.

Voici les résultats d’un point de référence qui compare le comportement du suivi et de l’absence de suivi pour une requête chargeant 10 blogs de 20 billets chacun. Le code source est disponible ici. N’hésitez pas à l’utiliser comme base de référence pour vos propres mesures.

Méthode NumBlogs NumPostsPerBlog Moyenne Erreur StdDev Median Taux RatioSD Gen 0 Gen1 Gen2 Affecté
AsTracking 10 20 1 414,7 us 27,20 us 45,44 us 1 405,5 us 1,00 0.00 60,5469 13,6719 - 380,11 Ko
AsNoTracking 10 20 993,3 us 24,04 us 65,40 us 966,2 us 0.71 0.05 37,1094 6,8359 - 232,89 Ko

Enfin, il est possible d’effectuer des mises à jour sans la surcharge du suivi des changements, en utilisant une requête sans suivi, puis en attachant l’instance retournée au contexte, tout en spécifiant les changements à apporter. Cela permet de transférer la charge du suivi des changements d’EF à l’utilisateur. L’opération doit être tentée uniquement si la surcharge du suivi des changements s’est révélée inacceptable via le profilage ou l’évaluation du point de référence.

En utilisant des requêtes SQL

Dans certains cas, il existe du code SQL plus optimisé pour votre requête, mais qui n’est pas généré par EF. Cela peut se produire quand la construction SQL est une extension spécifique à votre base de données, et qu’elle n’est pas prise en charge, ou tout simplement quand EF n’effectue pas encore la traduction. Dans ce cas, l’écriture manuelle du code SQL peut apporter une amélioration des performances considérables. EF prend en charge plusieurs façons d’y parvenir.

  • Utilisez des requêtes SQL directement dans votre requête, par exemple via FromSqlRaw. EF vous permet même de composer du code SQL avec des requêtes LINQ classiques, ce qui vous permet d’exprimer uniquement une partie de la requête en SQL. Il s’agit d’une bonne technique quand le code SQL doit uniquement être utilisé dans une seule requête de votre codebase.
  • Définissez une fonction UDF (fonction définie par l’utilisateur), puis appelez-la à partir de vos requêtes. Notez qu’EF permet aux fonctions UDF de retourner des jeux de résultats complets, appelés fonctions TVF (fonctions table), et qu’il permet également de mapper un DbSet à une fonction, ce qui la fait ressembler à une table.
  • Définissez une vue de base de données, et interrogez-la à partir de vos requêtes. Notez que contrairement aux fonctions, les vues ne peuvent pas accepter de paramètres.

Remarque

N’utilisez le code SQL brut qu’en dernier recours, après avoir vérifié qu’EF ne peut pas générer le code SQL souhaité, et quand les performances sont suffisamment importantes pour que la requête donnée le justifie. L’utilisation de code SQL brut présente des inconvénients considérables au niveau de la maintenance.

Programmation asynchrone

En règle générale, pour que votre application soit scalable, il est important de toujours utiliser des API asynchrones plutôt que synchrones (par exemple SaveChangesAsync au lieu de SaveChanges). Les API synchrones bloquent le thread pendant la durée des E/S de base de données, ce qui augmente le besoin en threads ainsi que le nombre de changements de contexte de thread à effectuer.

Pour plus d’informations, consultez la page relative à la programmation asynchrone.

Avertissement

Évitez de mélanger du code synchrone et asynchrone dans la même application. Il est très facile de déclencher par inadvertance des problèmes de privation de pool de threads, lesquels sont délicats à gérer.

Avertissement

L’implémentation asynchrone de Microsoft.Data.SqlClient présente malheureusement certains problèmes connus (par exemple n° 593, n° 601, etc.). Si vous rencontrez des problèmes de performances inattendus, essayez plutôt d’utiliser l’exécution des commandes de synchronisation, en particulier lorsque vous traitez de grandes valeurs de texte ou binaires.

Ressources supplémentaires