LINQ to SQL Listing 1: Beware of Server Round-Trips (SQL)
TLINQ to SQL generates this complex T-SQL query to reduce server round-trips from 922 (lazy loading) or 92 (eager loading) to two (eager loading). But it takes SQL Server 2005 more than three seconds to hydrate all Customer, Order, and Order_Detail entities from the Northwind persistence database (see Table 1, Test 3B). Depending on the round-trip cost, 92 queries, which execute in about 1.2 seconds, might be the optimum choice (Table 1, Test 2B).
SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID],
[t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate],
[t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].
[ShipAddress],
[t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode],
[t0].[ShipCountry], [t3].[OrderID] AS [OrderID2], [t3].
[ProductID],
[t3].[UnitPrice], [t3].[Quantity], [t3].[Discount], (
SELECT COUNT(*)
FROM [dbo].[Order Details] AS [t4]
WHERE [t4].[OrderID] = [t0].[OrderID]
) AS [count], [t2].[test], [t2].[CustomerID] AS
[CustomerID2],
[t2].[CompanyName], [t2].[ContactName], [t2].[ContactTitle],
[t2].[Address], [t2].[City], [t2].[Region], [t2].[PostalCode],
[t2].[Country], [t2].[Phone], [t2].[Fax]
FROM [dbo].[Orders] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[CustomerID], [t1].[CompanyName],
[t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City],
[t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax]
FROM [dbo].[Customers] AS [t1]
) AS [t2] ON [t2].[CustomerID] = [t0].[CustomerID]
LEFT OUTER JOIN [dbo].[Order Details] AS [t3]
ON [t3].[OrderID] = [t0].[OrderID]
WHERE [t0].[ShipCountry] = 'USA'
ORDER BY [t0].[OrderID], [t2].[CustomerID], [t3].[ProductID]