在我的项目中,我正在使用EntityFramework 4来处理数据。我发现一个简单的查询存在严重的性能问题。当我查看由EF4生成的sql查询的分析器时,我感到震惊。
我的实体数据模型中有一些表:
它看起来非常简单。我试图选择指定类别中的所有产品项目以及所有相关的导航属性。
我编写了这个LINQ查询:
我的实体数据模型中有一些表:
它看起来非常简单。我试图选择指定类别中的所有产品项目以及所有相关的导航属性。
我编写了这个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数据库。