Gestion des contenus localisés avec Entity Framework – Optimisation de requêtes
Il est fréquent d’avoir du contenu localisé dans une base de données. J’ai récemment eu besoin d’extraire de telles données avec Entity Framework, j’ai alors cherché une solution permettant d’avoir de bonnes performances.
Il existe plusieurs possibilités de modélisation de contenu localisé. Pour ma part, lorsque je dois stocker un objet localisable, je choisi de créer 2 tables : une table contenant les données non localisées et une table contenant les données localisées.
L’identifiant de langue que j’utilise correspond à la propriété LCID d’une culture .net. Pour rappel, une culture .net est constituée d’une langue et, éventuellement, d’une région. Il est ainsi possible d’avoir une culture française neutre, une culture française pour la France (fr-FR), etc. Vous trouverez ci-dessous un tableau listant les cultures que je vais utiliser pour ce post.
Ainsi, si je dois stocker des produits, j’aurais la modélisation suivante :
Je souhaite utiliser Entity Framework pour récupérer des produits avec le contenu localisé dans une culture donnée. Si le produit n’est pas localisé dans la culture demandée, je souhaite récupérer le contenu dans la culture parente ou la culture neutre.
Par exemple : soit un produit stocké dans la base avec les données localisées pour les cultures “fr”, “en-US” et “” (invariant). Si je demande ce produit en “fr-FR”, je souhaite obtenir le contenu “fr”. Si le produit est localisé dans aucune langue, je souhaite retourner null.
Comme ce genre de requête sera utilisé très fréquemment, j’ai cherché à optimiser les performances. Je me suis principalement concentré sur le nombre de pages lue. En effet, sur ma machine de développement, je n’ai pas de charge, le disque dur est donc peu sollicité, le temps d’exécution de la requête sera donc excellent. Le nombre de pages lues reste constant en fonction de la charge, c’est ce nombre qu’il faut réduire au maximum pour avoir une application qui a de bonnes performances en cas de forte charge.
Afin de faire mes tests, j’ai chargé la base avec un grand nombre de donnée, cela permettra de bien mettre en avant les problèmes IO. Les tables Product et ProductLocalized contiennent respectivement 1.2 et 6.3 millions de ligne.
Afin de ne pas travailler sur les 1.2 millions de ligne, je filtre ma requête par TypeId. Un TypeId est associé à environ 100 produits. Pour optimiser les requêtes, j’ai ajouté l’index suivant :
CREATE NONCLUSTERED INDEX [IX_Product_TypeId] ON [dbo].[Product]
(
[TypeId] ASC
)
INCLUDE ([ProductId], [SKU])
J’utilise SQL Server édition développeur et EF 4.0, chacun de mes tests sont joués plusieurs fois, le plan d’exécution est donc en cache. Les résultats affichés sont une moyenne des dernières exécutions. Les données proviennent de SQL Profiler.
La première requête que j’ai écrite a été la suivante :
Les performances de cette requête sont les suivantes :
J’ai ensuite modifié la requête pour factoriser les différents IF, ma requête est devenue la suivante :
Les résultats sont les suivants :
Cette requête est moitié moins couteuse que la première. Pour ce cas, Entity Framework ne mutualise pas les bouts d’expression identiques, il est donc plus optimisé de faire une pré-requête qui permettra de préselectionner les données.
La requête SQL et plan d’exécution correspondants sont les suivants :
Il y a 6 Index Seek dont 5 clustered . Le besoin est seulement de récupérer des données provenant de 2 tables, il doit être possible d’obtenir 2 Index Seek.
J’ai ensuite essayé la requête EF suivante :
La requête générée et le plan d’exécution sont les suivants :
Les performances sont les suivantes :
Le plan d’exécution n’a que 2 Index Seek, cependant les performances sont bien moins bonnes que la requête précédente. Le cout principal de la requête est un sort. Ce sort est fait sur un “case when”, il n’est donc pas possible d’ajouter un index pour optimiser la requête.
Je suis donc parti sur une toute autre piste en utilisant une jointure entre les cultures souhaitées et les enregistrements demandés. J’ai alors obtenu la requête suivante :
La syntaxe est un peu étrange, on fait une jointure entre un tableau d’objet anonyme en mémoire et la table ProductLocalized.
La requête SQL et le plan d'exécution correspondants sont les suivants :
Les résultats :
Le plan d’exécution parait complexe, cependant il n’y a 2 Index Seek, toutes les autres opérations sont des petites opérations sur des constantes ou autres. Les performances sont 10 fois meilleures que la première requête. J’ai essayé de faire la jointure dans l’autre sens, il n’y a pas eu de différence.
On voit donc qu’il est important de tester et analyser les différentes requêtes générée par Entity Framework. Il est souvent possible d’obtenir un même résultat en écrivant la requête de façon différente, l’analyse de la requête et de son plan d’exécution permet parfois des grands gains de performance. Par rapport à ma requête initiale, je suis passé de de 5000 pages lues à 480, soit un gain supérieur à 1000%.
Pour ma part, je ne pense pas qu’il soit possible d’optimiser davantage cette requête. Avez-vous une autre idée pour optimiser encore plus cette requête ? Que ce soit en SQL ou avec Entity Framework ?