Entity Framework 4中的Linq查询。性能差劲

Question

在我的项目中，我正在使用EntityFramework 4来处理数据。我发现一个简单的查询存在严重的性能问题。当我查看由EF4生成的sql查询的分析器时，我感到震惊。

我的实体数据模型中有一些表：

它看起来非常简单。我试图选择指定类别中的所有产品项目以及所有相关的导航属性。

我编写了这个LINQ查询：

ObjectSet<ProductItem> objectSet = ...; 
int categoryId = ...; 

var res = from pi in objectSet.Include("Product").Include("Inventory").Include("Inventory.Storage") 
where pi.Product.CategoryId == categoryId 
select pi;

EF生成了以下SQL查询语句：

SELECT   [Project1].[pintId1]          AS [pintId], 
[Project1].[pintId]           AS [pintId1], 
[Project1].[intProductId]     AS [intProductId], 
[Project1].[nvcSupplier]      AS [nvcSupplier], 
[Project1].[ nvcArticle]      AS [ nvcArticle], 
[Project1].[nvcBarcode]       AS [nvcBarcode], 
[Project1].[bIsActive]        AS [bIsActive], 
[Project1].[dtDeleted]        AS [dtDeleted], 
[Project1].[pintId2]          AS [pintId2], 
[Project1].[nvcName]          AS [nvcName], 
[Project1].[intCategoryId]    AS [intCategoryId], 
[Project1].[ncProductType]    AS [ncProductType], 
[Project1].[C1]               AS [C1], 
[Project1].[pintId3]          AS [pintId3], 
[Project1].[intProductItemId] AS [intProductItemId], 
[Project1].[intStorageId]     AS [intStorageId], 
[Project1].[dAmount]          AS [dAmount], 
[Project1].[mPrice]           AS [mPrice], 
[Project1].[dtModified]       AS [dtModified], 
[Project1].[pintId4]          AS [pintId4], 
[Project1].[nvcName1]         AS [nvcName1], 
[Project1].[bIsDefault]       AS [bIsDefault] 
FROM     (SELECT [Extent1].[pintId]         AS [pintId], 
[Extent1].[intProductId]   AS [intProductId], 
[Extent1].[nvcSupplier]    AS [nvcSupplier], 
[Extent1].[ nvcArticle]    AS [ nvcArticle], 
[Extent1].[nvcBarcode]     AS [nvcBarcode], 
[Extent1].[bIsActive]      AS [bIsActive], 
[Extent1].[dtDeleted]      AS [dtDeleted], 
[Extent2].[pintId]         AS [pintId1], 
[Extent3].[pintId]         AS [pintId2], 
[Extent3].[nvcName]        AS [nvcName], 
[Extent3].[intCategoryId]  AS [intCategoryId], 
[Extent3].[ncProductType]  AS [ncProductType], 
[Join3].[pintId1]          AS [pintId3], 
[Join3].[intProductItemId] AS [intProductItemId], 
[Join3].[intStorageId]     AS [intStorageId], 
[Join3].[dAmount]          AS [dAmount], 
[Join3].[mPrice]           AS [mPrice], 
[Join3].[dtModified]       AS [dtModified], 
[Join3].[pintId2]          AS [pintId4], 
[Join3].[nvcName]          AS [nvcName1], 
[Join3].[bIsDefault]       AS [bIsDefault], 
CASE 
WHEN ([Join3].[pintId1] IS NULL) THEN CAST(NULL AS int) 
ELSE 1 
END AS [C1] 
FROM   [ProductItem] AS [Extent1] 
INNER JOIN [Product] AS [Extent2] 
ON [Extent1].[intProductId] = [Extent2].[pintId] 
LEFT OUTER JOIN [Product] AS [Extent3] 
ON [Extent1].[intProductId] = [Extent3].[pintId] 
LEFT OUTER JOIN (SELECT [Extent4].[pintId]           AS [pintId1], 
[Extent4].[intProductItemId] AS [intProductItemId], 
[Extent4].[intStorageId]     AS [intStorageId], 
[Extent4].[dAmount]          AS [dAmount], 
[Extent4].[mPrice]           AS [mPrice], 
[Extent4].[dtModified]       AS [dtModified], 
[Extent5].[pintId]           AS [pintId2], 
[Extent5].[nvcName]          AS [nvcName], 
[Extent5].[bIsDefault]       AS [bIsDefault] 
FROM   [Inventory] AS [Extent4] 
INNER JOIN [Storage] AS [Extent5] 
ON [Extent4].[intStorageId] = [Extent5].[pintId]) AS [Join3] 
ON [Extent1].[pintId] = [Join3].[intProductItemId] 
WHERE  [Extent2].[intCategoryId] = 8 /* @p__linq__0 */) AS [Project1] 
ORDER BY [Project1].[pintId1] ASC, 
[Project1].[pintId] ASC, 
[Project1].[pintId2] ASC, 
[Project1].[C1] ASC

对于数据库中的7000条记录和特定类别中的约1000条记录，此查询的执行时间大约为10秒。如果看一下这个，就不足为奇了：

FROM [ProductItem] AS [Extent1]
INNER JOIN [Product] AS [Extent2]
ON [Extent1].[intProductId] = [Extent2].[pintId]
LEFT OUTER JOIN [Product] AS [Extent3]
ON [Extent1].[intProductId] = [Extent3].[pintId]
***LEFT OUTER JOIN (SELECT ....***

在join中嵌套select...太可怕了...我试图更改LINQ查询，但是输出的SQL查询结果相同。

对我来说，使用存储过程的解决方案是不可接受的，因为我正在使用SQL Compact数据库。

- Ivan

---

你的英语并不差 :) 这也是一个问得很好的问题。+1 - Aren

你可以使用 http://imgur.com/ 来分享图片。 - Steven

为什么要用到Include？直接使用“_from pi in objectSet where pi.Product.CategoryId == categoryId select pi_”不行吗？ - Steven Evers

如果您使用手写的SQL查询，性能是否更好？ - svick

+1 用来与手写的 SQL 进行比较。很难知道 SQL Compact 的整体性能（我肯定不知道）。 - Jaco Pretorius

Answer 1

你正在执行 Include("Product").Include("Inventory").Include("Inventory.Storage") 操作，惊讶于查询到了如此多的记录以及为什么SQL查询语句如此庞大？请确保你理解了 Include 方法的作用。如果你想要一个简单的查询，请使用以下代码：

var res =
    from pi in objectSet
    where pi.Product.CategoryId == categoryId 
    select pi;

请注意，这可能会使Products、Inventories和Storages懒加载，当您遍历这些子集合时会导致发送更多的查询。

- Steven

---

+1个好点 - 使用Product：ProductItem（1：）和ProductItem：Inventory（1：），单个Product将加载大量额外的（可能不需要的）数据...难怪如此缓慢... - marc_s

Answer 2

我认为问题出在存储元素中的库存集合。您的查询将限制所选Product、ProductItem和Inventory项目的CategoryId。然而，为了填充Storage元素的Inventory集合，查询还必须返回所有使用相同StorageId的Inventory行（然后是这些额外Inventory记录的所有对应ProductItem和Product行）。

我建议从Storage元素中移除Inventory集合或删除相应的include。

- Jeff Siver