While Jim Wooley is looking at performance in LINQ to SQL, I’ve finally pulled the VS2008 installation on my VPC out of the mysteries of SQL Server Express and installed SQL Server Developer Edition so that I can look at my queries as they come into profiler.

Note that this is all based on VS2008 Beta2 and Entity Framework Beta2. I’m using AdventureWorksLT as my test db.

QUERY #1

A Simple Query

Let’s just grab customers with this code:

Dim custs = aw.Customer

While I might lazily write TSQL that looks like

select * from Customer

EF creates a more explicit query, which makes perfect sense to me.

SELECT
0 AS [C1],
[Extent1].[CustomerID] AS [CustomerID],
[Extent1].[NameStyle] AS [NameStyle],
[Extent1].[Title] AS [Title],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[MiddleName] AS [MiddleName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Suffix] AS [Suffix],
[Extent1].[CompanyName] AS [CompanyName],
[Extent1].[SalesPerson] AS [SalesPerson],
[Extent1].[EmailAddress] AS [EmailAddress],
[Extent1].[Phone] AS [Phone],
[Extent1].[PasswordHash] AS [PasswordHash],
[Extent1].[PasswordSalt] AS [PasswordSalt],
[Extent1].[rowguid] AS [rowguid],
[Extent1].[ModifiedDate] AS [ModifiedDate]
FROM [SalesLT].[Customer] AS [Extent1]

QUERY #2

When I just want to get one particular record, I ask for there are a few ways to do this.

First is to write a query that returns the whole iQueryable with a where clause to filter for the single item. Then I iterate through that one item “list”. Using method or query syntax:

Dim cust = aw.Customer.Where(Function(c) c.CustomerID = 37)
For Each c In cust
   Dim name = c.CompanyName
Next

Dim cust2 = From c In aw.Customer Where c.CustomerID = 37 Select c
  For Each c In cust2
   Dim name = c.CompanyName
  Next

I get a reasonable TSQL query on the other end

SELECT
0 AS [C1],
[Extent1].[CustomerID] AS [CustomerID],
[Extent1].[NameStyle] AS [NameStyle],
[Extent1].[Title] AS [Title],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[MiddleName] AS [MiddleName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Suffix] AS [Suffix],
[Extent1].[CompanyName] AS [CompanyName],
[Extent1].[SalesPerson] AS [SalesPerson],
[Extent1].[EmailAddress] AS [EmailAddress],
[Extent1].[Phone] AS [Phone],
[Extent1].[PasswordHash] AS [PasswordHash],
[Extent1].[PasswordSalt] AS [PasswordSalt],
[Extent1].[rowguid] AS [rowguid],
[Extent1].[ModifiedDate] AS [ModifiedDate]
FROM [SalesLT].[Customer] AS [Extent1]
WHERE 37 = [Extent1].[CustomerID]

QUERY #3

But watch what happens to the TSQL when I make my query more efficient, regardless of method or query syntax.

Dim cust = aw.Customer.Where(Function(c) c.CustomerID = 37).First
 Dim name = cust.CompanyName

SELECT
[Limit1].[C1] AS [C1],
[Limit1].[CustomerID] AS [CustomerID],
[Limit1].[NameStyle] AS [NameStyle],
[Limit1].[Title] AS [Title],
[Limit1].[FirstName] AS [FirstName],
[Limit1].[MiddleName] AS [MiddleName],
[Limit1].[LastName] AS [LastName],
[Limit1].[Suffix] AS [Suffix],
[Limit1].[CompanyName] AS [CompanyName],
[Limit1].[SalesPerson] AS [SalesPerson],
[Limit1].[EmailAddress] AS [EmailAddress],
[Limit1].[Phone] AS [Phone],
[Limit1].[PasswordHash] AS [PasswordHash],
[Limit1].[PasswordSalt] AS [PasswordSalt],
[Limit1].[rowguid] AS [rowguid],
[Limit1].[ModifiedDate] AS [ModifiedDate]
FROM   ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]
LEFT OUTER JOIN  (SELECT TOP (1)
 [Extent1].[CustomerID] AS [CustomerID],
 [Extent1].[NameStyle] AS [NameStyle],
 [Extent1].[Title] AS [Title],
 [Extent1].[FirstName] AS [FirstName],
 [Extent1].[MiddleName] AS [MiddleName],
 [Extent1].[LastName] AS [LastName],
 [Extent1].[Suffix] AS [Suffix],
 [Extent1].[CompanyName] AS [CompanyName],
 [Extent1].[SalesPerson] AS [SalesPerson],
 [Extent1].[EmailAddress] AS [EmailAddress],
 [Extent1].[Phone] AS [Phone],
 [Extent1].[PasswordHash] AS [PasswordHash],
 [Extent1].[PasswordSalt] AS [PasswordSalt],
 [Extent1].[rowguid] AS [rowguid],
 [Extent1].[ModifiedDate] AS [ModifiedDate],
 0 AS [C1]
 FROM [SalesLT].[Customer] AS [Extent1]
 WHERE 37 = [Extent1].[CustomerID] ) AS [Limit1] ON cast(1 as bit) = cast(1 as bit)

What’s up with the outer join – to itself? Obviously by asking for First, which is an Element method, it changes the meaning of the query a lot. The more I think about it, if you need the flexibilitiy for First to be able to handle something like:

(From o in orders where o.Total>100 select o).First

where it’s not so obvious what the result may be as it is when filtering on a unique primary key.

I get the same effect if I use First this way, too:

Dim cust2 = From c In aw.Customer Where c.CustomerID = 37 Select c
  Dim cust = cust2.First

So that outer join is somehow necessary to pull it off. While I’d have to really push the envelope to see if there was any kind of performance difference to worry about, but it’s interesting to see the impact that using the method has on the TSQL.

QUERY #4 & QUERY #5

Next up is a table with foreign keys – 3 of them. I query for just one record (no children) in the SalesOrderHeader table.

I want to preface this by saying that I posted this on the ADO.NET Orcas forums here and was told

The Entity Framework (EF) team is aware of issues with this pattern (not the query, but the mapping). We should see huge improvements over the next couple of milestones. … The EF team is working to recognize and optimize for this common pattern, removing the redundant self-joins and including the foreign key values in a single projection.

But I didn’t want all of my exploration to go to waste, so this is what you’ll find today. We’ll just need to check back in later releases.

First I do it by getting the whole iQueryable then iterating through. Second method is to just use the First method and get only one object. I think you will quickly see why I pointed out the three foreign keys on this table.

Dim sorders = aw.SalesOrderHeader.Where(Function(so) so.SalesOrderID = 71797)
  For Each so In sorders
   Dim x = so.Comment
  Next

  Dim sorder2 = (From soh In aw.SalesOrderHeader Where soh.SalesOrderID = 71815).First
  Dim x2 = sorder2.Comment

The first query results in TSQL that creates outer joins for each foreign key, but it’s not too obnoxious.

The second is much crazier.

SELECT
[Limit1].[C1] AS [C1],
[Limit1].[SalesOrderID] AS [SalesOrderID],
[Limit1].[RevisionNumber] AS [RevisionNumber],
[Limit1].[OrderDate] AS [OrderDate],
[Limit1].[DueDate] AS [DueDate],
[Limit1].[Status] AS [Status],
[Limit1].[OnlineOrderFlag] AS [OnlineOrderFlag],
[Limit1].[SalesOrderNumber] AS [SalesOrderNumber],
[Limit1].[PurchaseOrderNumber] AS [PurchaseOrderNumber],
[Limit1].[AccountNumber] AS [AccountNumber],
[Limit1].[CreditCardApprovalCode] AS [CreditCardApprovalCode],
[Limit1].[SubTotal] AS [SubTotal],
[Limit1].[TaxAmt] AS [TaxAmt],
[Limit1].[Freight] AS [Freight],
[Limit1].[TotalDue] AS [TotalDue],
[Limit1].[Comment] AS [Comment],
[Limit1].[rowguid] AS [rowguid],
[Limit1].[ModifiedDate] AS [ModifiedDate],
[Limit1].[ShipMethod] AS [ShipMethod],
[Limit1].[ShipDate] AS [ShipDate],
[Project3].[C1] AS [C2],
[Project3].[BillToAddressID] AS [BillToAddressID],
[Project5].[C1] AS [C3],
[Project5].[ShipToAddressID] AS [ShipToAddressID],
[Project7].[C1] AS [C4],
[Project7].[CustomerID] AS [CustomerID]
FROM      ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]
LEFT OUTER JOIN  (SELECT TOP (1)
 [Extent1].[SalesOrderID] AS [SalesOrderID],
 [Extent1].[RevisionNumber] AS [RevisionNumber],
 [Extent1].[OrderDate] AS [OrderDate],
 [Extent1].[DueDate] AS [DueDate],
 [Extent1].[ShipDate] AS [ShipDate],
 [Extent1].[Status] AS [Status],
 [Extent1].[OnlineOrderFlag] AS [OnlineOrderFlag],
 [Extent1].[SalesOrderNumber] AS [SalesOrderNumber],
 [Extent1].[PurchaseOrderNumber] AS [PurchaseOrderNumber],
 [Extent1].[AccountNumber] AS [AccountNumber],
 [Extent1].[ShipMethod] AS [ShipMethod],
 [Extent1].[CreditCardApprovalCode] AS [CreditCardApprovalCode],
 [Extent1].[SubTotal] AS [SubTotal],
 [Extent1].[TaxAmt] AS [TaxAmt],
 [Extent1].[Freight] AS [Freight],
 [Extent1].[TotalDue] AS [TotalDue],
 [Extent1].[Comment] AS [Comment],
 [Extent1].[rowguid] AS [rowguid],
 [Extent1].[ModifiedDate] AS [ModifiedDate],
 0 AS [C1]
 FROM [SalesLT].[SalesOrderHeader] AS [Extent1]
 WHERE 71815 = [Extent1].[SalesOrderID] ) AS [Limit1] ON cast(1 as bit) = cast(1 as bit)
LEFT OUTER JOIN  (SELECT
 [Extent2].[BillToAddressID] AS [BillToAddressID],
 2 AS [C1]
 FROM  [SalesLT].[SalesOrderHeader] AS [Extent2]
 INNER JOIN  (SELECT [Limit2].[SalesOrderID] AS [SalesOrderID], [Limit2].[C1] AS [C1]
  FROM ( SELECT TOP (1)
   [Extent3].[SalesOrderID] AS [SalesOrderID],
   0 AS [C1]
   FROM [SalesLT].[SalesOrderHeader] AS [Extent3]
   WHERE 71815 = [Extent3].[SalesOrderID]
  )  AS [Limit2]
  WHERE [Limit2].[C1] = 0 ) AS [Filter3] ON [Filter3].[SalesOrderID] = [Extent2].[SalesOrderID]
 WHERE [Extent2].[BillToAddressID] IS NOT NULL ) AS [Project3] ON cast(1 as bit) = cast(1 as bit)
LEFT OUTER JOIN  (SELECT
 [Extent4].[ShipToAddressID] AS [ShipToAddressID],
 2 AS [C1]
 FROM  [SalesLT].[SalesOrderHeader] AS [Extent4]
 INNER JOIN  (SELECT [Limit3].[SalesOrderID] AS [SalesOrderID], [Limit3].[C1] AS [C1]
  FROM ( SELECT TOP (1)
   [Extent5].[SalesOrderID] AS [SalesOrderID],
   0 AS [C1]
   FROM [SalesLT].[SalesOrderHeader] AS [Extent5]
   WHERE 71815 = [Extent5].[SalesOrderID]
  )  AS [Limit3]
  WHERE [Limit3].[C1] = 0 ) AS [Filter6] ON [Filter6].[SalesOrderID] = [Extent4].[SalesOrderID]
 WHERE [Extent4].[ShipToAddressID] IS NOT NULL ) AS [Project5] ON cast(1 as bit) = cast(1 as bit)
LEFT OUTER JOIN  (SELECT
 [Extent6].[CustomerID] AS [CustomerID],
 3 AS [C1]
 FROM  [SalesLT].[SalesOrderHeader] AS [Extent6]
 INNER JOIN  (SELECT TOP (1)
  [Extent7].[SalesOrderID] AS [SalesOrderID],
  0 AS [C1]
  FROM [SalesLT].[SalesOrderHeader] AS [Extent7]
  WHERE 71815 = [Extent7].[SalesOrderID] ) AS [Limit4] ON [Limit4].[SalesOrderID] = [Extent6].[SalesOrderID]
 WHERE [Limit4].[C1] = 0 ) AS [Project7] ON cast(1 as bit) = cast(1 as bit)

QUERY #6 & QUERY #7 (#4 & #5 converted to C#)

Now before you go blaming this on VB parsing :-), I ran the last two queries in C#:

 var so1 = aw.SalesOrderHeader.Where(so => so.SalesOrderID == 71797);
 foreach (var so in so1)
   Debug.Print(so.Comment);

  var so2 = (from soh in aw.SalesOrderHeader where soh.SalesOrderID == 71815 select soh).First();
  Debug.Print(so2.Comment);

And these resulted in the same T-SQL as their VB counterparts.

There’s more that I’ve been looking at but this post is way too long already!