mnachu (1/5/2009)

---

SQL Server : 2005.

Database: AdentureWorks.

.NET version 3.6 with SP1, EF is included in .NET. Using VS 2008 with SP1.

I am trying some examples in Microsoft Entity Framework. I noticed most of the queries generated by EF looks different for even simple selects. For example the following EF query is selecting rows from Employee with Order by and one extra column:

SET STATISTICS IO ON

SET STATISTICS TIME ON

SELECT

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

[Project1].[EmployeeID] AS [EmployeeID],

[Project1].[NationalIDNumber] AS [NationalIDNumber],

[Project1].[LoginID] AS [LoginID],

[Project1].[Title] AS [Title],

[Project1].[BirthDate] AS [BirthDate],

[Project1].[MaritalStatus] AS [MaritalStatus],

[Project1].[Gender] AS [Gender],

[Project1].[HireDate] AS [HireDate],

[Project1].[SalariedFlag] AS [SalariedFlag],

[Project1].[VacationHours] AS [VacationHours],

[Project1].[SickLeaveHours] AS [SickLeaveHours],

[Project1].[CurrentFlag] AS [CurrentFlag],

[Project1].[rowguid] AS [rowguid],

[Project1].[ModifiedDate] AS [ModifiedDate],

[Project1].[ContactID] AS [ContactID],

[Project1].[ManagerID] AS [ManagerID]

FROM ( SELECT

[Extent1].[EmployeeID] AS [EmployeeID],

[Extent1].[NationalIDNumber] AS [NationalIDNumber],

[Extent1].[ContactID] AS [ContactID],

[Extent1].[LoginID] AS [LoginID],

[Extent1].[ManagerID] AS [ManagerID],

[Extent1].[Title] AS [Title],

[Extent1].[BirthDate] AS [BirthDate],

[Extent1].[MaritalStatus] AS [MaritalStatus],

[Extent1].[Gender] AS [Gender],

[Extent1].[HireDate] AS [HireDate],

[Extent1].[SalariedFlag] AS [SalariedFlag],

[Extent1].[VacationHours] AS [VacationHours],

[Extent1].[SickLeaveHours] AS [SickLeaveHours],

[Extent1].[CurrentFlag] AS [CurrentFlag],

[Extent1].[rowguid] AS [rowguid],

[Extent1].[ModifiedDate] AS [ModifiedDate],

1 AS [C1]

FROM [HumanResources].[Employee] AS [Extent1]

) AS [Project1]

ORDER BY [Project1].[HireDate] ASC

As you would have noticed EF is adding a subquery. If I had written this query I would have done something like this:

SET STATISTICS IO ON

SET STATISTICS TIME ON

SELECT

1 AS [C1],

[Project1].[EmployeeID] AS [EmployeeID],

[Project1].[NationalIDNumber] AS [NationalIDNumber],

[Project1].[LoginID] AS [LoginID],

[Project1].[Title] AS [Title],

[Project1].[BirthDate] AS [BirthDate],

[Project1].[MaritalStatus] AS [MaritalStatus],

[Project1].[Gender] AS [Gender],

[Project1].[HireDate] AS [HireDate],

[Project1].[SalariedFlag] AS [SalariedFlag],

[Project1].[VacationHours] AS [VacationHours],

[Project1].[SickLeaveHours] AS [SickLeaveHours],

[Project1].[CurrentFlag] AS [CurrentFlag],

[Project1].[rowguid] AS [rowguid],

[Project1].[ModifiedDate] AS [ModifiedDate],

[Project1].[ContactID] AS [ContactID],

[Project1].[ManagerID] AS [ManagerID]

FROM [HumanResources].[Employee] AS [Project1]

ORDER BY [Project1].[HireDate] ASC

Both the queries give same result. I could slightly optimize my query. But I am not going to touch that part for now.

My question is when I look at the IO details both queries does the same and they both run pretty much at same speed.

Question is 1) Even though I see syntactical difference why don't I see any performance different? (or I am missing something here).

2) Is there a clear answer to which one is better here?

If there are other articles please ignore this one and direct me there.

Thanks,

Nachi