cultivate passion for everything else that goes on around programming
During an optimization session for a ASP.NET application based on EntityFramework (and Linq to Entities) I found out a simple optimization method based on rewriting the Linq to Entities queries for many-to-many objects.
In a simple scenario, we have CareTypes, Establishments and a many-to-many table between them: EstablishmentCareTypes. If we want to fetch all CareTypes for a given Establishment, we would write the following TSQL query:
1: select * from CareTypes ct
2: inner join EstablishmentCareTypes ect on ect.CareTypeId = ct.CareTypeId
3: where ect.EstablishmentId='6B555389-3DAC-4B42-9CB4-903A572AED09'
Using SQL Server Management Studio you can see the estimated execution plan, which is rather simple:
In Linq to Entities, the query was written like this:
1: var careTypes = (from e in context.EstablishmentSet
2: where e.EstablishmentId == establishmentId
3: select e.CareTypes.OrderBy(ct => ct.Name)).FirstOrDefault();
This query does the job, but has the following generated TSQL:
1: SELECT
2: [Project3].[EstablishmentId] AS [EstablishmentId],
3: [Project3].[C1] AS [C1],
4: [Project3].[StatusId] AS [StatusId],
5: [Project3].[Description] AS [Description],
6: [Project3].[LastEditTime] AS [LastEditTime],
7: [Project3].[LastEditedBy] AS [LastEditedBy],
8: [Project3].[CareTypeId] AS [CareTypeId],
9: [Project3].[Name] AS [Name],
10: [Project3].[Parameter1] AS [Parameter1],
11: [Project3].[Parameter2] AS [Parameter2],
12: [Project3].[Parameter3] AS [Parameter3],
13: [Project3].[Parameter4] AS [Parameter4],
14: [Project3].[Parameter5] AS [Parameter5],
15: [Project3].[Parameter6] AS [Parameter6],
16: [Project3].[Parameter7] AS [Parameter7],
17: [Project3].[Parameter8] AS [Parameter8],
18: [Project3].[Parameter9] AS [Parameter9],
19: [Project3].[Parameter10] AS [Parameter10],
20: [Project3].[Parameter11] AS [Parameter11],
21: [Project3].[Parameter12] AS [Parameter12],
22: [Project3].[Parameter13] AS [Parameter13],
23: [Project3].[Parameter14] AS [Parameter14],
24: [Project3].[NrChildren] AS [NrChildren],
25: [Project3].[NrHoursPerWeek] AS [NrHoursPerWeek],
26: [Project3].[ArrivingTime] AS [ArrivingTime],
27: [Project3].[LeavingTime] AS [LeavingTime],
28: [Project3].[ArrivingTimeInterruption] AS [ArrivingTimeInterruption],
29: [Project3].[LeavingTimeInterruption] AS [LeavingTimeInterruption],
30: [Project3].[PersonIndex] AS [PersonIndex],
31: [Project3].[HasInterruption] AS [HasInterruption],
32: [Project3].[PersonIndexAtNoon] AS [PersonIndexAtNoon]
33: FROM ( SELECT
34: [Limit1].[EstablishmentId] AS [EstablishmentId],
35: [Project2].[CareTypeId] AS [CareTypeId],
36: [Project2].[StatusId] AS [StatusId],
37: [Project2].[Description] AS [Description],
38: [Project2].[LastEditTime] AS [LastEditTime],
39: [Project2].[LastEditedBy] AS [LastEditedBy],
40: [Project2].[Name] AS [Name],
41: [Project2].[Parameter1] AS [Parameter1],
42: [Project2].[Parameter2] AS [Parameter2],
43: [Project2].[Parameter3] AS [Parameter3],
44: [Project2].[Parameter4] AS [Parameter4],
45: [Project2].[Parameter5] AS [Parameter5],
46: [Project2].[Parameter6] AS [Parameter6],
47: [Project2].[Parameter7] AS [Parameter7],
48: [Project2].[Parameter8] AS [Parameter8],
49: [Project2].[Parameter9] AS [Parameter9],
50: [Project2].[Parameter10] AS [Parameter10],
51: [Project2].[Parameter11] AS [Parameter11],
52: [Project2].[Parameter12] AS [Parameter12],
53: [Project2].[Parameter13] AS [Parameter13],
54: [Project2].[Parameter14] AS [Parameter14],
55: [Project2].[NrChildren] AS [NrChildren],
56: [Project2].[NrHoursPerWeek] AS [NrHoursPerWeek],
57: [Project2].[ArrivingTime] AS [ArrivingTime],
58: [Project2].[LeavingTime] AS [LeavingTime],
59: [Project2].[ArrivingTimeInterruption] AS [ArrivingTimeInterruption],
60: [Project2].[LeavingTimeInterruption] AS [LeavingTimeInterruption],
61: [Project2].[PersonIndex] AS [PersonIndex],
62: [Project2].[HasInterruption] AS [HasInterruption],
63: [Project2].[PersonIndexAtNoon] AS [PersonIndexAtNoon],
64: [Project2].[C1] AS [C1]
65: FROM (SELECT TOP (1)
66: [Extent1].[EstablishmentId] AS [EstablishmentId]
67: FROM [dbo].[Establishments] AS [Extent1]
68: WHERE [Extent1].[EstablishmentId] = '6B555389-3DAC-4B42-9CB4-903A572AED09' ) AS [Limit1]
69: LEFT OUTER JOIN (SELECT
70: [Extent2].[EstablishmentId] AS [EstablishmentId],
71: [Extent3].[CareTypeId] AS [CareTypeId],
72: [Extent3].[StatusId] AS [StatusId],
73: [Extent3].[Description] AS [Description],
74: [Extent3].[LastEditTime] AS [LastEditTime],
75: [Extent3].[LastEditedBy] AS [LastEditedBy],
76: [Extent3].[Name] AS [Name],
77: [Extent3].[Parameter1] AS [Parameter1],
78: [Extent3].[Parameter2] AS [Parameter2],
79: [Extent3].[Parameter3] AS [Parameter3],
80: [Extent3].[Parameter4] AS [Parameter4],
81: [Extent3].[Parameter5] AS [Parameter5],
82: [Extent3].[Parameter6] AS [Parameter6],
83: [Extent3].[Parameter7] AS [Parameter7],
84: [Extent3].[Parameter8] AS [Parameter8],
85: [Extent3].[Parameter9] AS [Parameter9],
86: [Extent3].[Parameter10] AS [Parameter10],
87: [Extent3].[Parameter11] AS [Parameter11],
88: [Extent3].[Parameter12] AS [Parameter12],
89: [Extent3].[Parameter13] AS [Parameter13],
90: [Extent3].[Parameter14] AS [Parameter14],
91: [Extent3].[NrChildren] AS [NrChildren],
92: [Extent3].[NrHoursPerWeek] AS [NrHoursPerWeek],
93: [Extent3].[ArrivingTime] AS [ArrivingTime],
94: [Extent3].[LeavingTime] AS [LeavingTime],
95: [Extent3].[ArrivingTimeInterruption] AS [ArrivingTimeInterruption],
96: [Extent3].[LeavingTimeInterruption] AS [LeavingTimeInterruption],
97: [Extent3].[PersonIndex] AS [PersonIndex],
98: [Extent3].[HasInterruption] AS [HasInterruption],
99: [Extent3].[PersonIndexAtNoon] AS [PersonIndexAtNoon],
100: 1 AS [C1]
101: FROM (SELECT
102: [EstablishmentCareTypes].[EstablishmentId] AS [EstablishmentId],
103: [EstablishmentCareTypes].[CareTypeId] AS [CareTypeId]
104: FROM [dbo].[EstablishmentCareTypes] AS [EstablishmentCareTypes]) AS [Extent2]
105: INNER JOIN [dbo].[CareTypes] AS [Extent3] ON [Extent3].[CareTypeId] = [Extent2].[CareTypeId] ) AS [Project2] ON [Limit1].[EstablishmentId] = [Project2].[EstablishmentId]
106: ) AS [Project3]
107: ORDER BY [Project3].[EstablishmentId] ASC, [Project3].[C1] ASC, [Project3].[Name] ASC
This has a rather complicated execution plan:
Obviously not what would be expected after knowing the execution plan of our manually written TSQL!
After some Internet research, I wrote the Linq to Entities query like this:
1: var careTypes = (from e in context.EstablishmentSet
2: from ct in e.CareTypes
3: where e.EstablishmentId == establishmentId
4: orderby ct.Name
5: select ct);
This returns the exact same objects, but has the execution plan we would expect:
The generated TSQL is also much simpler (and shorter):
1: SELECT
2: [Extent2].[StatusId] AS [StatusId],
3: [Extent2].[Description] AS [Description],
4: [Extent2].[LastEditTime] AS [LastEditTime],
5: [Extent2].[LastEditedBy] AS [LastEditedBy],
6: [Extent2].[CareTypeId] AS [CareTypeId],
7: [Extent2].[Name] AS [Name],
8: [Extent2].[Parameter1] AS [Parameter1],
9: [Extent2].[Parameter2] AS [Parameter2],
10: [Extent2].[Parameter3] AS [Parameter3],
11: [Extent2].[Parameter4] AS [Parameter4],
12: [Extent2].[Parameter5] AS [Parameter5],
13: [Extent2].[Parameter6] AS [Parameter6],
14: [Extent2].[Parameter7] AS [Parameter7],
15: [Extent2].[Parameter8] AS [Parameter8],
16: [Extent2].[Parameter9] AS [Parameter9],
17: [Extent2].[Parameter10] AS [Parameter10],
18: [Extent2].[Parameter11] AS [Parameter11],
19: [Extent2].[Parameter12] AS [Parameter12],
20: [Extent2].[Parameter13] AS [Parameter13],
21: [Extent2].[Parameter14] AS [Parameter14],
22: [Extent2].[NrChildren] AS [NrChildren],
23: [Extent2].[NrHoursPerWeek] AS [NrHoursPerWeek],
24: [Extent2].[ArrivingTime] AS [ArrivingTime],
25: [Extent2].[LeavingTime] AS [LeavingTime],
26: [Extent2].[ArrivingTimeInterruption] AS [ArrivingTimeInterruption],
27: [Extent2].[LeavingTimeInterruption] AS [LeavingTimeInterruption],
28: [Extent2].[PersonIndex] AS [PersonIndex],
29: [Extent2].[HasInterruption] AS [HasInterruption],
30: [Extent2].[PersonIndexAtNoon] AS [PersonIndexAtNoon]
31: FROM (SELECT
32: [EstablishmentCareTypes].[EstablishmentId] AS [EstablishmentId],
33: [EstablishmentCareTypes].[CareTypeId] AS [CareTypeId]
34: FROM [dbo].[EstablishmentCareTypes] AS [EstablishmentCareTypes]) AS [Extent1]
35: INNER JOIN [dbo].[CareTypes] AS [Extent2] ON [Extent1].[CareTypeId] = [Extent2].[CareTypeId]
36: WHERE [Extent1].[EstablishmentId] = '6B555389-3DAC-4B42-9CB4-903A572AED09'
The SQL Server Profiler also shows a big improvement over the previous query:
Before:
After:
The difference between the two queries, related to the effort done by SQL Server to process them, is astonishing. If the 2 tables referred were very large, having a lot of users accessing the ASP.NET application would bring the SQL Server eventually to its limits.
By also noticing that some queries executed twice for the same request and by introducing caching, the performance gain is considerable.
Important to this type of improvement described earlier is that it’s risk free. It is very easy to rewrite these queries, without having to think about introducing possible bugs.
How about :
var careTypes = from e in context.EstablishmentSet
join ct from e.CareTypes on e.CareTypeId equals ct.CareTypeId
where e.EstablishmentId == establishmentId
orderby ct.Name
select ct
?
This is a classic InnerJoin in LINQ.
Actually this won’t work, as described here: http://stackoverflow.com/questions/1097992/linq-to-entities-many-to-many-select-query (see second answer) The join would work in the case that the many-to-many table EstablishmentCareTypes is also accessible in the ObjectContext. In this case it isn’t, because it only contains two foreign keys and EF hides it for the user.