Une plongée approfondie dans les performances d'Entity Framework lors de l'utilisation de "Contient"

Dans mon travail quotidien, j'utilise Entity Framework. C'est très pratique, mais dans certains cas, ses performances sont lentes. Bien qu'il y ait beaucoup de bons articles sur l'amélioration des performances EF et que de très bons conseils utiles soient donnés (par exemple, évitez les requêtes complexes, les paramètres dans Skip and Take, utilisez les vues, sélectionnez uniquement les champs nécessaires, etc.), pas grand-chose ne peut vraiment être fait lorsque vous devez utiliser un complexe Contains sur deux champs ou plus, en d'autres termes, lorsque vous joignez des données à une liste de mémoire .

Problème

Vérifions l'exemple suivant :

 var localData = GetDataFromApiOrUser(); var query = from p in context.Prices join s in context.Securities on p.SecurityId equals s.SecurityId join t in localData on new { s.Ticker, p.TradedOn, p.PriceSourceId } equals new { t.Ticker, t.TradedOn, t.PriceSourceId } select p; var result = query.ToList();

Le code ci-dessus ne fonctionne pas du tout dans EF 6, et bien qu'il fonctionne dans EF Core, la jointure est en fait effectuée localement - puisque j'ai dix millions d'enregistrements dans ma base de données, ils sont tous téléchargés et toute la mémoire est consommée . Ce n'est pas un bogue dans EF. C'est attendu. Cependant, ne serait-ce pas fantastique s'il y avait quelque chose pour résoudre ce problème ? Dans cet article, je vais faire quelques expériences avec une approche différente pour contourner ce goulot d'étranglement des performances.

Solution

Je vais essayer différentes manières d'y parvenir en partant de la plus simple à la plus avancée. À chaque étape, je fournirai du code et des métriques, telles que le temps pris et l'utilisation de la mémoire. Notez que j'interromprai l'exécution du programme d'analyse comparative s'il fonctionne plus de dix minutes.

Le code du programme d'analyse comparative se trouve dans le référentiel suivant. Il utilise C#, .NET Core, EF Core et PostgreSQL. J'ai utilisé une machine avec Intel Core i5, 8 Go de RAM et un SSD.

Le schéma de base de données pour les tests ressemble à ceci :

Option 1. Simple et naïf

Essayons quelque chose de simple, juste pour commencer.

 var result = new List<Price>(); using (var context = CreateContext()) { foreach (var testElement in TestData) { result.AddRange(context.Prices.Where( x => x.Security.Ticker == testElement.Ticker && x.TradedOn == testElement.TradedOn && x.PriceSourceId == testElement.PriceSourceId)); } }

L'algorithme est simple : pour chaque élément des données de test, recherchez un élément approprié dans la base de données et ajoutez-le à la collection de résultats. Ce code n'a qu'un avantage : il est très facile à mettre en œuvre. De plus, il est lisible et maintenable. Son inconvénient évident est qu'il est le plus lent. Même si les trois colonnes sont indexées, la surcharge de la communication réseau crée toujours un goulot d'étranglement des performances. Voici les métriques :

Ainsi, pour un gros volume, cela prend environ une minute. La consommation mémoire semble raisonnable.

Option 2. Naïf avec parallèle

Essayons maintenant d'ajouter du parallélisme au code. L'idée de base ici est que frapper la base de données dans des threads parallèles peut améliorer les performances globales.

 var result = new ConcurrentBag<Price>(); var partitioner = Partitioner.Create(0, TestData.Count); Parallel.ForEach(partitioner, range => { var subList = TestData.Skip(range.Item1) .Take(range.Item2 - range.Item1) .ToList(); using (var context = CreateContext()) { foreach (var testElement in subList) { var query = context.Prices.Where( x => x.Security.Ticker == testElement.Ticker && x.TradedOn == testElement.TradedOn && x.PriceSourceId == testElement.PriceSourceId); foreach (var el in query) { result.Add(el); } } } });

Il est intéressant de noter que, pour les ensembles de données de test plus petits, cette approche fonctionne plus lentement que la première solution, mais pour les échantillons plus grands, elle est plus rapide (environ 2 fois dans ce cas). La consommation de mémoire change un peu, mais pas de manière significative.

Option 3. Plusieurs contenus

Essayons une autre approche :

• Préparez 3 collections de valeurs uniques de Ticker, PriceSourceId et Date.
• Effectuez la requête en une seule exécution en filtrant à l'aide de 3 Contient.
• Revérifiez localement (voir ci-dessous).

 var result = new List<Price>(); using (var context = CreateContext()) { var tickers = TestData.Select(x => x.Ticker).Distinct().ToList(); var dates = TestData.Select(x => x.TradedOn).Distinct().ToList(); var ps = TestData.Select(x => x.PriceSourceId) .Distinct().ToList(); var data = context.Prices .Where(x => tickers.Contains(x.Security.Ticker) && dates.Contains(x.TradedOn) && ps.Contains(x.PriceSourceId)) .Select(x => new { x.PriceSourceId, Price = x, Ticker = x.Security.Ticker, }) .ToList(); var lookup = data.ToLookup(x => $"{x.Ticker}, {x.Price.TradedOn}, {x.PriceSourceId}"); foreach (var el in TestData) { var key = $"{el.Ticker}, {el.TradedOn}, {el.PriceSourceId}"; result.AddRange(lookup[key].Select(x => x.Price)); } }

Cette approche est problématique. Le temps d'exécution dépend beaucoup des données. Il peut récupérer uniquement les enregistrements requis (auquel cas il sera très rapide), mais il peut en renvoyer beaucoup plus (peut-être même 100 fois plus).

Considérons les données de test suivantes :

Ici, j'interroge les prix de Ticker1 négocié le 2018-01-01 et de Ticker2 négocié le 2018-01-02. Cependant, quatre enregistrements seront en fait renvoyés.

Les valeurs uniques pour Ticker sont Ticker1 et Ticker2 . Les valeurs uniques pour TradedOn sont 2018-01-01 et 2018-01-02 .

Ainsi, quatre enregistrements correspondent à cette expression.

C'est pourquoi une revérification locale est nécessaire et pourquoi cette approche est dangereuse. Les mesures sont les suivantes :

Affreuse consommation de mémoire ! Les tests avec de gros volumes ont échoué en raison d'un délai d'attente de 10 minutes.

Option 4. Générateur de prédicats

Changeons de paradigme : construisons une bonne vieille Expression pour chaque ensemble de données de test.

 var result = new List<Price>(); using (var context = CreateContext()) { var baseQuery = from p in context.Prices join s in context.Securities on p.SecurityId equals s.SecurityId select new TestData() { Ticker = s.Ticker, TradedOn = p.TradedOn, PriceSourceId = p.PriceSourceId, PriceObject = p }; var tradedOnProperty = typeof(TestData).GetProperty("TradedOn"); var priceSourceIdProperty = typeof(TestData).GetProperty("PriceSourceId"); var tickerProperty = typeof(TestData).GetProperty("Ticker"); var paramExpression = Expression.Parameter(typeof(TestData)); Expression wholeClause = null; foreach (var td in TestData) { var elementClause = Expression.AndAlso( Expression.Equal( Expression.MakeMemberAccess( paramExpression, tradedOnProperty), Expression.Constant(td.TradedOn) ), Expression.AndAlso( Expression.Equal( Expression.MakeMemberAccess( paramExpression, priceSourceIdProperty), Expression.Constant(td.PriceSourceId) ), Expression.Equal( Expression.MakeMemberAccess( paramExpression, tickerProperty), Expression.Constant(td.Ticker)) )); if (wholeClause == null) wholeClause = elementClause; else wholeClause = Expression.OrElse(wholeClause, elementClause); } var query = baseQuery.Where( (Expression<Func<TestData, bool>>)Expression.Lambda( wholeClause, paramExpression)).Select(x => x.PriceObject); result.AddRange(query); }

Le code résultant est assez complexe. Construire des expressions n'est pas la chose la plus facile et implique une réflexion (qui, elle-même, n'est pas si rapide). Mais cela nous aide à construire une seule requête en utilisant beaucoup de … (.. AND .. AND ..) OR (.. AND .. AND ..) OR (.. AND .. AND ..) ... . Voici les résultats :

Encore pire que l'une ou l'autre des approches précédentes.

Option 5. Table de données de requête partagée

Essayons une autre approche :

J'ai ajouté une nouvelle table à la base de données qui contiendra les données de requête. Pour chaque requête, je peux maintenant :

• Commencer une transaction (si pas encore commencée)
• Télécharger les données de requête dans cette table (temporaire)
• Effectuer une requête
• Annuler une transaction—pour supprimer les données téléchargées

 var result = new List<Price>(); using (var context = CreateContext()) { context.Database.BeginTransaction(); var reducedData = TestData.Select(x => new SharedQueryModel() { PriceSourceId = x.PriceSourceId, Ticker = x.Ticker, TradedOn = x.TradedOn }).ToList(); // Here query data is stored to shared table context.QueryDataShared.AddRange(reducedData); context.SaveChanges(); var query = from p in context.Prices join s in context.Securities on p.SecurityId equals s.SecurityId join t in context.QueryDataShared on new { s.Ticker, p.TradedOn, p.PriceSourceId } equals new { t.Ticker, t.TradedOn, t.PriceSourceId } select p; result.AddRange(query); context.Database.RollbackTransaction(); }

Les métriques d'abord :

Le résultat est très bon. Très vite. La consommation de mémoire est également bonne. Mais les inconvénients sont :

• Vous devez créer une table supplémentaire dans la base de données pour effectuer un seul type de requête,
• Vous devez démarrer une transaction (qui consomme de toute façon des ressources SGBD), et
• Vous devez écrire quelque chose dans la base de données (dans une opération READ !) - et fondamentalement, cela ne fonctionnera pas si vous utilisez quelque chose comme un réplica en lecture.

Mais à part cela, cette approche est agréable, rapide et lisible. Et un plan de requête est mis en cache dans ce cas !

Option 6. Extension MemoryJoin

Ici, je vais utiliser un package NuGet appelé EntityFrameworkCore.MemoryJoin. Malgré le fait que son nom contient le mot Core, il prend également en charge EF 6. Il s'appelle MemoryJoin, mais en fait, il envoie les données de requête spécifiées en tant que VALUES au serveur et tout le travail est effectué sur le serveur SQL.

Vérifions le code.

 var result = new List<Price>(); using (var context = CreateContext()) { // better to select needed properties only, for better performance var reducedData = TestData.Select(x => new { x.Ticker, x.TradedOn, x.PriceSourceId }).ToList(); var queryable = context.FromLocalList(reducedData); var query = from p in context.Prices join s in context.Securities on p.SecurityId equals s.SecurityId join t in queryable on new { s.Ticker, p.TradedOn, p.PriceSourceId } equals new { t.Ticker, t.TradedOn, t.PriceSourceId } select p; result.AddRange(query); }

Métrique:

Cela a l'air génial. Trois fois plus rapide que l'approche précédente, ce qui en fait la plus rapide à ce jour. 3,5 secondes pour 64 000 enregistrements ! Le code est simple et compréhensible. Cela fonctionne avec les répliques en lecture seule. Vérifions la requête générée pour trois éléments :

 SELECT "p"."PriceId", "p"."ClosePrice", "p"."OpenPrice", "p"."PriceSourceId", "p"."SecurityId", "p"."TradedOn", "t"."Ticker", "t"."TradedOn", "t"."PriceSourceId" FROM "Price" AS "p" INNER JOIN "Security" AS "s" ON "p"."SecurityId" = "s"."SecurityId" INNER JOIN ( SELECT "x"."string1" AS "Ticker", "x"."date1" AS "TradedOn", CAST("x"."long1" AS int4) AS "PriceSourceId" FROM ( SELECT * FROM ( VALUES (1, @__gen_q_p0, @__gen_q_p1, @__gen_q_p2), (2, @__gen_q_p3, @__gen_q_p4, @__gen_q_p5), (3, @__gen_q_p6, @__gen_q_p7, @__gen_q_p8) ) AS __gen_query_data__ (id, string1, date1, long1) ) AS "x" ) AS "t" ON (("s"."Ticker" = "t"."Ticker") AND ("p"."PriceSourceId" = "t"."PriceSourceId")

Comme vous pouvez le constater, cette fois, les valeurs réelles sont transmises de la mémoire au serveur SQL dans la construction VALUES. Et cela fait l'affaire : le serveur SQL a réussi à effectuer une opération de jointure rapide et à utiliser correctement les index.

Cependant, il y a quelques inconvénients (vous pouvez en lire plus sur mon blog):

• Vous devez ajouter un DbSet supplémentaire à votre modèle (mais pas besoin de le créer dans la base de données)
• L'extension ne prend pas en charge les classes de modèle avec de nombreuses propriétés : trois propriétés de chaîne, trois propriétés de date, trois propriétés de guide, trois propriétés float/double et trois propriétés int/byte/long/decimal. C'est plus que suffisant dans 90% des cas, je suppose. Cependant, si ce n'est pas le cas, vous pouvez créer une classe personnalisée et l'utiliser. Donc, CONSEIL : vous devez transmettre les valeurs réelles dans une requête, sinon les ressources sont gaspillées.

Conclusion

Parmi les choses que j'ai testées ici, j'opterais certainement pour MemoryJoin. Quelqu'un d'autre pourrait objecter que les inconvénients sont insurmontables, et comme tous ne peuvent pas être résolus pour le moment, nous devrions nous abstenir d'utiliser l'extension. Eh bien, pour moi, c'est comme dire qu'il ne faut pas utiliser de couteau parce qu'on pourrait se couper. L'optimisation n'était pas une tâche pour les développeurs juniors mais pour quelqu'un qui comprend le fonctionnement d'EF. À cette fin, cet outil peut améliorer considérablement les performances. Qui sait? Peut-être qu'un jour, quelqu'un chez Microsoft ajoutera un support de base pour les VALEURS dynamiques.

Enfin, voici quelques diagrammes supplémentaires pour comparer les résultats.

Vous trouverez ci-dessous un diagramme du temps nécessaire pour effectuer une opération. MemoryJoin est le seul qui fait le travail dans un délai raisonnable. Seules quatre approches peuvent traiter de gros volumes : deux implémentations naïves, une table partagée et MemoryJoin.

Le diagramme suivant concerne la consommation de mémoire. Toutes les approches montrent plus ou moins les mêmes nombres, sauf celle avec plusieurs Contains . Ce phénomène a été décrit ci-dessus.