Bienvenue à Blogs CodeS-SourceS Identification | Inscription | Aide

Abonnements

L2E : impact sur le SQL

Comme je l’écrivais hier,

“Derrière la magie d’Entity Framework se cache la réalité du SQL. Je persiste et je signe, si vous ne connaissez pas le SQL, il sera très compliqué d’écrire des requêtes L2E optimales.

J’ai trouvé une bonne illustrations avec les fonctions de grouping.”

Hier, je vous ai montré comment exécuter un Count et un Max en une seule requête LINQ.

Maintenant imaginons que je veuille toujours récupérer le nombre de commandes et la date de la dernière commande mais par client.

Comment récupérer cela?

Si vous êtes sûr que chaque client à au moins une commande ou que vous voulez ignorer les clients sans commande, la meilleure façon d’écrire cette requête est probablement celle-ci:

from o in context.Orders
group o by o.Customer into g
select new { Customer = g.Key, OrdersCount = g.Count(), LastOrderDate = g.Max(o => o.OrderDate) };

Dans ce cas, le SQL est très proche de celui de la requête L2E :

SELECT

[GroupBy1].[K1] AS [CustomerId],

[GroupBy1].[K2] AS [LastName],

[GroupBy1].[K3] AS [FirstName],

[GroupBy1].[K4] AS [BirthDay],

[GroupBy1].[K5] AS [AddressLine],

[GroupBy1].[K6] AS [City],

[GroupBy1].[K7] AS [PostalCode],

[GroupBy1].[K8] AS [Region],

[GroupBy1].[K9] AS [Country],

[GroupBy1].[A1] AS [C1],

[GroupBy1].[A2] AS [C2]

FROM ( SELECT

[Extent2].[CustomerId] AS [K1],

[Extent2].[LastName] AS [K2],

[Extent2].[FirstName] AS [K3],

[Extent2].[BirthDay] AS [K4],

[Extent2].[AddressLine] AS [K5],

[Extent2].[City] AS [K6],

[Extent2].[PostalCode] AS [K7],

[Extent2].[Region] AS [K8],

[Extent2].[Country] AS [K9],

COUNT(1) AS [A1],

MAX([Extent1].[OrderDate]) AS [A2]

FROM [dbo].[Orders] AS [Extent1]

INNER JOIN [dbo].[Customers] AS [Extent2] ON [Extent1].[CustomerId] = [Extent2].[CustomerId]

GROUP BY [Extent2].[CustomerId], [Extent2].[LastName], [Extent2].[FirstName], [Extent2].[BirthDay], [Extent2].[AddressLine], [Extent2].[City], [Extent2].[PostalCode], [Extent2].[Region], [Extent2].[Country]

) AS [GroupBy1]

Avec un plan d’exécution assez simple :

image_thumb[7]

Si vous pouvez avoir des clients sans commandes, la requêtes devient plus compliquée.

Une idée assez simple serait d’utiliser la requête précédente union les clients sans commandes :

(from o in context.Orders
 group o by o.Customer into g
 select new { Customer = g.Key, OrdersCount = g.Count(), LastOrderDate = (DateTime?)g.Max(o => o.OrderDate) }).Union(
from c in context.Customers
where !c.Orders.Any()
select new { Customer = c, OrdersCount = 0, LastOrderDate = (DateTime?)null });

Dans ce cas, la requête SQL n’est pas performante :

SELECT

[Distinct1].[C1] AS [C1],

[Distinct1].[C2] AS [C2],

[Distinct1].[C3] AS [C3],

[Distinct1].[C4] AS [C4],

[Distinct1].[C5] AS [C5],

[Distinct1].[C6] AS [C6],

[Distinct1].[C7] AS [C7],

[Distinct1].[C8] AS [C8],

[Distinct1].[C9] AS [C9],

[Distinct1].[C10] AS [C10],

[Distinct1].[C11] AS [C11],

[Distinct1].[C12] AS [C12]

FROM ( SELECT DISTINCT

[UnionAll1].[C1] AS [C1],

[UnionAll1].[CustomerId] AS [C2],

[UnionAll1].[LastName] AS [C3],

[UnionAll1].[FirstName] AS [C4],

[UnionAll1].[BirthDay] AS [C5],

[UnionAll1].[AddressLine] AS [C6],

[UnionAll1].[City] AS [C7],

[UnionAll1].[PostalCode] AS [C8],

[UnionAll1].[Region] AS [C9],

[UnionAll1].[Country] AS [C10],

[UnionAll1].[C2] AS [C11],

[UnionAll1].[C3] AS [C12]

FROM (SELECT

1 AS [C1],

[GroupBy1].[K1] AS [CustomerId],

[GroupBy1].[K2] AS [LastName],

[GroupBy1].[K3] AS [FirstName],

[GroupBy1].[K4] AS [BirthDay],

[GroupBy1].[K5] AS [AddressLine],

[GroupBy1].[K6] AS [City],

[GroupBy1].[K7] AS [PostalCode],

[GroupBy1].[K8] AS [Region],

[GroupBy1].[K9] AS [Country],

[GroupBy1].[A1] AS [C2],

CAST( [GroupBy1].[A2] AS datetime2) AS [C3]

FROM ( SELECT

[Extent2].[CustomerId] AS [K1],

[Extent2].[LastName] AS [K2],

[Extent2].[FirstName] AS [K3],

[Extent2].[BirthDay] AS [K4],

[Extent2].[AddressLine] AS [K5],

[Extent2].[City] AS [K6],

[Extent2].[PostalCode] AS [K7],

[Extent2].[Region] AS [K8],

[Extent2].[Country] AS [K9],

COUNT(1) AS [A1],

MAX([Extent1].[OrderDate]) AS [A2]

FROM [dbo].[Orders] AS [Extent1]

INNER JOIN [dbo].[Customers] AS [Extent2] ON [Extent1].[CustomerId] = [Extent2].[CustomerId]

GROUP BY [Extent2].[CustomerId], [Extent2].[LastName], [Extent2].[FirstName], [Extent2].[BirthDay], [Extent2].[AddressLine], [Extent2].[City], [Extent2].[PostalCode], [Extent2].[Region], [Extent2].[Country]

) AS [GroupBy1]

UNION ALL

SELECT

1 AS [C1],

[Extent3].[CustomerId] AS [CustomerId],

[Extent3].[LastName] AS [LastName],

[Extent3].[FirstName] AS [FirstName],

[Extent3].[BirthDay] AS [BirthDay],

[Extent3].[AddressLine] AS [AddressLine],

[Extent3].[City] AS [City],

[Extent3].[PostalCode] AS [PostalCode],

[Extent3].[Region] AS [Region],

[Extent3].[Country] AS [Country],

0 AS [C2],

CAST(NULL AS datetime2) AS [C3]

FROM [dbo].[Customers] AS [Extent3]

WHERE NOT EXISTS (SELECT

1 AS [C1]

FROM [dbo].[Orders] AS [Extent4]

WHERE [Extent3].[CustomerId] = [Extent4].[CustomerId]

)) AS [UnionAll1]

) AS [Distinct1]

image_thumb[10]

Vous vous en doutez, j’espère, ce n’est probablement pas le meilleur moyen d’écrire cette requête…

Un autre serait d’utiliser ce que j’ai présenté hier : un group by sur une constant pour récupérer le count et le max sur c.Orders:

from c in context.Customers
let ordersInfo = (from o in c.Orders
                  group o by 1 into g
                  select new { OrdersCount = g.Count(), LastOrderDate = g.Max(o => o.OrderDate) }).FirstOrDefault()
select new { Customer = c, OrdersCount = (int?)ordersInfo.OrdersCount ?? 0, OrderDate = (DateTime?)ordersInfo.LastOrderDate };

Remarquez le cast en int? ou DateTime?. Contrairement à .NET, SQL retourne null pour OrdersCount et OrderDate si un client n’a pas de commande.

Si cette requête est bonne en L2O, elle ne l’est pas en L2E au vu du SQL généré !

SELECT

[Extent1].[CustomerId] AS [CustomerId],

[Extent1].[LastName] AS [LastName],

[Extent1].[FirstName] AS [FirstName],

[Extent1].[BirthDay] AS [BirthDay],

[Extent1].[AddressLine] AS [AddressLine],

[Extent1].[City] AS [City],

[Extent1].[PostalCode] AS [PostalCode],

[Extent1].[Region] AS [Region],

[Extent1].[Country] AS [Country],

CASE WHEN ([Limit1].[C1] IS NULL) THEN 0 ELSE [Limit1].[C1] END AS [C1],

CAST( [Limit1].[C2] AS datetime2) AS [C2]

FROM [dbo].[Customers] AS [Extent1]

OUTER APPLY (SELECT TOP (1)

[GroupBy1].[A1] AS [C1],

[GroupBy1].[A2] AS [C2]

FROM ( SELECT

[Project1].[K1] AS [K1],

COUNT([Project1].[A1]) AS [A1],

MAX([Project1].[A2]) AS [A2]

FROM ( SELECT

1 AS [K1],

1 AS [A1],

[Project1].[OrderDate] AS [A2]

FROM ( SELECT

[Extent2].[OrderDate] AS [OrderDate]

FROM [dbo].[Orders] AS [Extent2]

WHERE [Extent1].[CustomerId] = [Extent2].[CustomerId]

) AS [Project1]

) AS [Project1]

GROUP BY [K1]

) AS [GroupBy1] ) AS [Limit1]

image_thumb[12]

Comme vous pouvez le voir, cette requête est pire que la précédente. Donc contrairement à mon post d’hier, le group par une constant n’est pas une bonne idée ici.

On peut également essayer comme ceci :

from c in context.Customers
let orders = c.Orders
select new { Customer = c, OrdersCount = (int?)orders.Count ?? 0, LastOrderDate = (DateTime?)orders.Max(o => o.OrderDate) };

Mais le SQL généré n’est pas meilleur :

SELECT

[Project3].[CustomerId] AS [CustomerId],

[Project3].[LastName] AS [LastName],

[Project3].[FirstName] AS [FirstName],

[Project3].[BirthDay] AS [BirthDay],

[Project3].[AddressLine] AS [AddressLine],

[Project3].[City] AS [City],

[Project3].[PostalCode] AS [PostalCode],

[Project3].[Region] AS [Region],

[Project3].[Country] AS [Country],

CASE WHEN ([Project3].[C1] IS NULL) THEN 0 ELSE [Project3].[C2] END AS [C1],

CAST( [Project3].[C3] AS datetime2) AS [C2]

FROM ( SELECT

[Project2].[CustomerId] AS [CustomerId],

[Project2].[LastName] AS [LastName],

[Project2].[FirstName] AS [FirstName],

[Project2].[BirthDay] AS [BirthDay],

[Project2].[AddressLine] AS [AddressLine],

[Project2].[City] AS [City],

[Project2].[PostalCode] AS [PostalCode],

[Project2].[Region] AS [Region],

[Project2].[Country] AS [Country],

[Project2].[C1] AS [C1],

[Project2].[C2] AS [C2],

(SELECT

MAX([Extent4].[OrderDate]) AS [A1]

FROM [dbo].[Orders] AS [Extent4]

WHERE [Project2].[CustomerId] = [Extent4].[CustomerId]) AS [C3]

FROM ( SELECT

[Project1].[CustomerId] AS [CustomerId],

[Project1].[LastName] AS [LastName],

[Project1].[FirstName] AS [FirstName],

[Project1].[BirthDay] AS [BirthDay],

[Project1].[AddressLine] AS [AddressLine],

[Project1].[City] AS [City],

[Project1].[PostalCode] AS [PostalCode],

[Project1].[Region] AS [Region],

[Project1].[Country] AS [Country],

[Project1].[C1] AS [C1],

(SELECT

COUNT(1) AS [A1]

FROM [dbo].[Orders] AS [Extent3]

WHERE [Project1].[CustomerId] = [Extent3].[CustomerId]) AS [C2]

FROM ( SELECT

[Extent1].[CustomerId] AS [CustomerId],

[Extent1].[LastName] AS [LastName],

[Extent1].[FirstName] AS [FirstName],

[Extent1].[BirthDay] AS [BirthDay],

[Extent1].[AddressLine] AS [AddressLine],

[Extent1].[City] AS [City],

[Extent1].[PostalCode] AS [PostalCode],

[Extent1].[Region] AS [Region],

[Extent1].[Country] AS [Country],

(SELECT

COUNT(1) AS [A1]

FROM [dbo].[Orders] AS [Extent2]

WHERE [Extent1].[CustomerId] = [Extent2].[CustomerId]) AS [C1]

FROM [dbo].[Customers] AS [Extent1]

) AS [Project1]

) AS [Project2]

) AS [Project3]

Quant-au plan d’exécution, il pique les yeux :

image_thumb[16]

Ok j’arrête de jouer avec votre patience. Quelle est la meilleure requête ?

Je pense que c’est la suivante :

from c in context.Customers
from o in
    (from o2 in context.Orders
     where o2.CustomerId == c.CustomerId
     group o2 by o2.CustomerId into g
     select new { OrdersCount = g.Count(), LastOrderDate = (DateTime?)g.Max(o => o.OrderDate) }).DefaultIfEmpty()
select new { Customer = c, OrdersCount = (int?)o.OrdersCount ?? 0, o.LastOrderDate };

Avec celle-ci la requête SQL et le plan d’exécution reste simple et efficace :

SELECT

[Extent1].[CustomerId] AS [CustomerId],

[Extent1].[LastName] AS [LastName],

[Extent1].[FirstName] AS [FirstName],

[Extent1].[BirthDay] AS [BirthDay],

[Extent1].[AddressLine] AS [AddressLine],

[Extent1].[City] AS [City],

[Extent1].[PostalCode] AS [PostalCode],

[Extent1].[Region] AS [Region],

[Extent1].[Country] AS [Country],

CASE WHEN ([GroupBy1].[A1] IS NULL) THEN 0 ELSE [GroupBy1].[A1] END AS [C1],

CASE WHEN ([GroupBy1].[K1] IS NULL) THEN CAST(NULL AS datetime2) ELSE CAST( [GroupBy1].[A2] AS datetime2) END AS [C2]

FROM [dbo].[Customers] AS [Extent1]

OUTER APPLY (SELECT

[Extent2].[CustomerId] AS [K1],

COUNT(1) AS [A1],

MAX([Extent2].[OrderDate]) AS [A2]

FROM [dbo].[Orders] AS [Extent2]

WHERE [Extent2].[CustomerId] = [Extent1].[CustomerId]

GROUP BY [Extent2].[CustomerId] ) AS [GroupBy1]

image_thumb[18]

Notez le coût de la requête 11%, comme la première requête qui ne remontait pas les clients sans commande ! Ca rocks !

 

Pourquoi ais-je écris ce post ? Pourquoi ais-je montré le SQL généré ou les plans d’exécution liés ?

Je pense que c’est important. Contrairement au discours marketing, je ne pense pas qu’EF soit magique et facile à utiliser. Bien sûr, pour une base de démo avec 3 rows dans 3 tables, vous pouvez utiliser la requête que vous voulez mais pour une application de la vraie vie avec de vraies problématique de performance, ce n’est pas si simple… Attention, je continue de trouver EF fantastique et je continue d’adorer ça. Cependant, je pense qu’il est important de comprendre les impactes des requêtes L2E sur le SQL et ce n’est pas simple…

Avec ce post, je voulais montrer à quel point les plans d’exécution du SQL peuvent être différent pour un même résultat en fonction de la manière d’écrire la requête L2E.

Je réalise souvent des audits sur Entity Framework, particulièrement pour résoudre des problèmes de performance et je n’ai, à ce jour, jamais vu de vrais problèmes liés à EF. Les problèmes de performance avec EF que j’ai pu observer étaient tous liés à une méconnaissance d’EF et / ou une mauvaise conception de la base.

Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :

Publié mercredi 15 juin 2011 00:48 par Matthieu MEZIL

Commentaires

# re: L2E : impact sur le SQL @ jeudi 16 juin 2011 08:53

Bonjour Mathieu,

merci pour ton post.

Je pense comme toi qu'il est nécessaire de connaitre et maitriser le langage SQL pour créer des applications performantes avec EF. C'est également vrai à mon sens sur les autres ORM du marché.

Deux questions me viennent à l'esprit si tu le permet :

1) As tu un retour d’expérience sur des tools qui permettent d'améliorer (de manière constante) la qualité du code EF d'une équipe pendant la phase de développement? Je pense à EFProf (

http://efprof.com) mais peut-être il y en a-t-il d'autre.

2) On ne t'entends pas beaucoup parler (voir pas du tout) de EF Code First. Tu es fâché avec cette troisième façon de faire?

fabrice.michellonet

# re: L2E : impact sur le SQL @ jeudi 16 juin 2011 11:06

Salut Fabrice

1) Non d'ailleurs pour être honnête, je n'ai jamais pris le temps de tester EFProf bien que je connaisse, bien sûr, son existence.

2) Non non je ne suis pas fâché. J'ai  d'ailleurs réalisé des projets avec et j'avais prévu de l'utiliser dans l'après-midi du dev TPL (qui a finalement été annulé :().

J'avoue que je suis en constante opposition avec la team EF sur la priorisation des tâches et que si j'avais dû faire moi-même la gestion des priorités, on n'aurait que le DB First pour l'instant... Cependant, vu que je semble être le seul à avoir ce point de vue, je dois probablement avoir tort…

S'il est vrai que j'étais assez réticent à l'approche Code First et Model First au début, je pense désormais que celles-ci ont leurs avantages et leurs inconvénients et que, dans certains cas, les avantages sont bien réels.

En revanche, dans la plus part des cas, je continue d'être hostile à l'idée de générer la base. Cependant les approches Code First et même Model First n'imposent pas cette génération.

Si je n'en parle pour ainsi dire pas, comme tu me le fais justement remarquer, c'est essentiellement parce que je n'ai pas trouvé de choses très intéressantes à dire dessus. Une des raisons vient probablement du fait que j'ai cruellement manqué de temps pour réellement m'y plonger. L'utilisation que j'en ai faite est restée très basique jusqu'à maintenant.

Je prends un réel plaisir à bloguer sur des sujets pointus et je refuse pour l'instant de me "forcer" à bloguer des choses que je juge trop simples. C'est la raison principale pour laquelle je ne parle pas de Code First.

Matthieu MEZIL

# re: L2E : impact sur le SQL @ lundi 20 juin 2011 12:32

@Mathieu>Non tu n'es pas le seul à avoir ce point de vue sur CodeFirst.

Je trouve cette approche beaucoup plus naturelle que de passer par un modèle.

Ce que je souhaite c'est créer mes classes, et avoir un ORM qui permet de simplement persister mes instances et ceci dans cet ordre là.

Et concernant le fait de blogger des choses simples, je penses que tu as tord. Tu devrais blogger des choses simples pour montrer qu'EF peut être accessible et non blogger des sapins de noël avec du code illisible qui va décourager bon nombre de développeurs.

++

Patrice

patrice

Les commentaires anonymes sont désactivés

Les 10 derniers blogs postés

- Merci par Blog de Jérémy Jeanson le 10-01-2019, 20:47

- Office 365: Script PowerShell pour auditer l’usage des Office Groups de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 11:02

- Office 365: Script PowerShell pour auditer l’usage de Microsoft Teams de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 10:39

- Office 365: Script PowerShell pour auditer l’usage de OneDrive for Business de votre tenant par Blog Technique de Romelard Fabrice le 04-25-2019, 15:13

- Office 365: Script PowerShell pour auditer l’usage de SharePoint Online de votre tenant par Blog Technique de Romelard Fabrice le 02-27-2019, 13:39

- Office 365: Script PowerShell pour auditer l’usage d’Exchange Online de votre tenant par Blog Technique de Romelard Fabrice le 02-25-2019, 15:07

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Stream Portal par Blog Technique de Romelard Fabrice le 02-21-2019, 17:56

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Video Portal par Blog Technique de Romelard Fabrice le 02-18-2019, 18:56

- Office 365: Script PowerShell pour extraire les Audit Log basés sur des filtres fournis par Blog Technique de Romelard Fabrice le 01-28-2019, 16:13

- SharePoint Online: Script PowerShell pour désactiver l’Option IRM des sites SPO non autorisés par Blog Technique de Romelard Fabrice le 12-14-2018, 13:01