Entity Framework Generated SQL Queries

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.

Advertisements