Database Reference
In-Depth Information
[Extent3].[Qty] AS [Qty],
[Extent3].[Price] AS [Price],
CASE
WHEN ([Extent3].[OrderItemId] IS NULL)
THEN CAST(NULL AS int)
ELSE 1
END AS [C1]
FROM (
SELECT TOP (1)
[Extent1].[OrderId] AS [OrderId],
[Extent1].[CustomerId] AS [CustomerId1],
[Extent1].[OrderNo] AS [OrderNo],
[Extent2].[CustomerId] AS [CustomerId2],
[Extent2].[FirstName] AS [FirstName],
[Extent2].[LastName] AS [LastName],
[Extent2].[Email] AS [Email],
[Extent2].[LastPurchaseDate] AS [LastPurchaseDate],
[Extent2].[CreditLimit] AS [CreditLimit],
[Extent2].[Photo] AS [Photo]
FROM [dbo].[Orders] AS [Extent1]
INNER JOIN [dbo].[Customers] AS [Extent2] ON
[Extent1].[CustomerId] = [Extent2].[CustomerId]
WHERE 1 = [Extent1].[OrderId]
) AS [Limit1]
LEFT OUTER JOIN [dbo].[OrderItems] AS [Extent3] ON
[Limit1].[OrderId] = [Extent3].[OrderId]
) AS [Project1]
ORDER BY [Project1].[OrderId] ASC, [Project1].[CustomerId1] ASC,
[Project1].[C1] ASC
Keeping the supportability aspect out of discussion, you can see that this query is inefficient. Rather than reading
Orders
and
OrderItems
data separately from each other, Entity Framework joined all of the tables together including
the
Orders
and
Customers
attributes with every
OrderItems
row. This behavior can introduce significant overhead
to SQL Server and the network, especially if some columns contain large amounts of data. The
Photo
column in the
Customers
table is a perfect example of such a situation.
Parameterization
Correct parameterization is, perhaps, the most important factor that contributes to the success of the framework.
As I already mentioned, incorrectly parameterized queries add to the CPU load on the server due to recompilations,
increases the size of plan cache, and results in inefficient execution plans due to the implicit data type conversions.
It is extremely important to understand how frameworks handle parameters and to avoid inefficiencies
whenever possible.
There are several important questions related to parameterization, such as when the framework uses parameters
and how it chooses parameter data types and length.
Listing 16-12 generates several queries using the integer
Customer.CreditLimit
column in the predicate.
