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] 
comments powered by Disqus
Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.