CodingPleasure

cultivate passion for everything else that goes on around programming

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:

image

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:

image

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:

image

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:

image

After:

image

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

2 responses to “Entity Framework Generated SQL Queries

  1. OBP October 7, 2010 at 16:01

    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.

  2. mcanti October 7, 2010 at 16:20

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: