linq 查询实体_优化实体框架Linq查询

最新推荐文章于 2024-06-28 08:00:00 发布

cunchi8090

最新推荐文章于 2024-06-28 08:00:00 发布

阅读量439

点赞数

文章标签： java mysql python 数据库 hive

原文链接：https://www.experts-exchange.com/articles/12338/Optimizing-Entity-Framework-Linq-query.html

版权

linq 查询实体

介绍 (Introduction)

Using Linq and Entity Framework may result in very slow operations.

使用Linq和Entity Framework可能会导致操作非常缓慢。

Our 1st reflection might be: "To bad EF is too slow and can't be used in production applications."

我们的第一React可能是：“ EF太差了，它太慢了，不能在生产应用程序中使用。”

But, we may wish to consider rechecking our code…

但是，我们不妨考虑重新检查我们的代码…

背景 (Background )

在我的一个应用程序中，用户经历了很长的操作（有时超过1分钟），这对于生产应用程序是不可接受的。

I had created a Bug for that, and start tracking the issue. One very useful tool I use to track long queries through EF is SQL Server Profiler. This can help to see how many records are sent by SQL Server to the application, and check if it makes sense with the results expected.

我为此创建了一个Bug，并开始跟踪问题。我用来通过EF跟踪长时间查询的一个非常有用的工具是SQL Server Profiler。这可以帮助查看SQL Server向应用程序发送了多少条记录，并检查它是否符合预期的结果。

I use LinqPad application, too, to execute my Linq query easily.

我也使用LinqPad应用程序来轻松执行Linq查询。

查询的初始版本 (Initial version of the query)

After tracking my code I isolated the query that was taking a lot of time, and copy-and-paste it into LinqPad :

跟踪我的代码后，我隔离了要花费很多时间的查询，并将其复制并粘贴到LinqPad中：

Dim result As Boolean

result = (From w In packEntity.SenderPacks Select w.Sales.Any).Any

result.dump

Execution time : 24.512s

执行时间：24.512s

As we can see 24 sec just to rest a Boolean, it certainly to long.

正如我们看到的那样，仅保留一个布尔值就需要24秒，这肯定很长。

Let's see the query and execution plan:

让我们看一下查询和执行计划：

-- Region Parameters
DECLARE @EntityKeyValue1 BigInt = 1000
-- EndRegion
SELECT 
[Extent1].[PKID] AS [PKID], 
[Extent1].[LaboratoireCode] AS [LaboratoireCode], 
[Extent1].[CentraleCode] AS [CentraleCode], 
[Extent1].[CentraleProduit] AS [CentraleProduit], 
[Extent1].[ProduitProdCode] AS [ProduitProdCode], 
[Extent1].[ProduitPresCode] AS [ProduitPresCode], 
[Extent1].[CentraleProduitDescription] AS [CentraleProduitDescription], 
[Extent1].[CentraleProduitCoeff] AS [CentraleProduitCoeff], 
[Extent1].[CentraleProduitDateCreation] AS [CentraleProduitDateCreation], 
[Extent1].[CentraleProduitDateDerniereUtilisation] AS [CentraleProduitDateDerniereUtilisation], 
[Extent1].[CentraleProduitSupprime] AS [CentraleProduitSupprime], 
[Extent1].[CentraleProduitDateSuppression] AS [CentraleProduitDateSuppression], 
[Extent1].[CentraleProduitCommentaire] AS [CentraleProduitCommentaire], 
[Extent1].[CentraleProduitCatégorie] AS [CentraleProduitCatégorie], 
[Extent1].[DPFTEMPFK_MAND_IDSender] AS [DPFTEMPFK_MAND_IDSender], 
[Extent1].[IDPack] AS [IDPack], 
[Extent1].[missingparent_current_levelmin] AS [missingparent_current_levelmin], 
[Extent1].[missingparent_current_monthmin] AS [missingparent_current_monthmin], 
[Extent1].[missingparent_new_levelmin] AS [missingparent_new_levelmin], 
[Extent1].[CentraleProduitQTECoeff] AS [CentraleProduitQTECoeff]
FROM [dbo].[ProduitsCentrales] AS [Extent1]
WHERE [Extent1].[IDPack] = @EntityKeyValue1
GO

-- Region Parameters
DECLARE @EntityKeyValue1 NVarChar(1000) = '601'
DECLARE @EntityKeyValue2 NVarChar(1000) = '401'
DECLARE @EntityKeyValue3 NVarChar(1000) = '4434319'
-- EndRegion
SELECT 
[Extent1].[PKID] AS [PKID], 
[Extent1].[LaboratoireCode] AS [LaboratoireCode], 
[Extent1].[CentraleCode] AS [CentraleCode], 
[Extent1].[CentraleClient] AS [CentraleClient], 
[Extent1].[CentraleProduit] AS [CentraleProduit], 
[Extent1].[DateVente] AS [DateVente], 
[Extent1].[QT] AS [QT], 
[Extent1].[CA] AS [CA], 
[Extent1].[Gratuit] AS [Gratuit], 
[Extent1].[AncienTrt] AS [AncienTrt], 
[Extent1].[InterCo] AS [InterCo], 
[Extent1].[InterCoAdh] AS [InterCoAdh], 
[Extent1].[TradeBrand] AS [TradeBrand], 
[Extent1].[DPFTEMPFK_NULL_IDSender] AS [DPFTEMPFK_NULL_IDSender], 
[Extent1].[DPFTEMPFK_MAND_IDProjectSale] AS [DPFTEMPFK_MAND_IDProjectSale], 
[Extent1].[DPFTEMPFK_MAND_IDSenderParticipant] AS [DPFTEMPFK_MAND_IDSenderParticipant], 
[Extent1].[DPFTEMPFK_MAND_IDSenderPack] AS [DPFTEMPFK_MAND_IDSenderPack], 
[Extent1].[missingparent_current_levelmin] AS [missingparent_current_levelmin], 
[Extent1].[missingparent_current_monthmin] AS [missingparent_current_monthmin], 
[Extent1].[missingparent_new_levelmin] AS [missingparent_new_levelmin], 
[Extent1].[CentraleVendeur] AS [CentraleVendeur], 
[Extent1].[DPFTEMPFK_MAND_IDSenderSeller] AS [DPFTEMPFK_MAND_IDSenderSeller], 
[Extent1].[PrixUnitaire] AS [PrixUnitaire], 
[Extent1].[EstCalcule] AS [EstCalcule], 
[Extent1].[QTCalcule] AS [QTCalcule], 
[Extent1].[CACalcule] AS [CACalcule], 
[Extent1].[PrixUnitaireCalcule] AS [PrixUnitaireCalcule], 
[Extent1].[CATarifaireCalcule] AS [CATarifaireCalcule], 
[Extent1].[CATarifaireCalculeNormalise] AS [CATarifaireCalculeNormalise]
FROM [dbo].[Ventes] AS [Extent1]
WHERE ([Extent1].[LaboratoireCode] = @EntityKeyValue1) AND ([Extent1].[CentraleCode] = @EntityKeyValue2) AND ([Extent1].[CentraleProduit] = @EntityKeyValue3)

The issue is:

问题是：

As we use "packEntity.SenderPacks", EF will load all "packEntity.SenderPacks" entities and after query for each entity "Sales.Any"

当我们使用“ packEntity.SenderPacks”时，EF将加载所有“ packEntity.SenderPacks”实体，并在查询每个实体“ Sales.Any”之后

It results on a big data transfer for nothing.

这样就可以进行大数据传输，而不进行任何操作。

我对查询的第二个版本： (My 2nd version on the query:)

Dim result As Boolean

result = (from r in (From p In Packs
					Where p.PKID = 1000
					From w In p.SenderPacks
					Select w.Sales.Any) 
		where r = True).any

result.dump

Execution time 0.016s

执行时间0.016s

The SqlQuery:

SELECT 
CASE WHEN ( EXISTS (SELECT 
	1 AS [C1]
	FROM ( SELECT 
		CASE WHEN ( EXISTS (SELECT 
			1 AS [C1]
			FROM [dbo].[Ventes] AS [Extent2]
			WHERE ([Extent1].[LaboratoireCode] = [Extent2].[LaboratoireCode]) AND ([Extent1].[CentraleCode] = [Extent2].[CentraleCode]) AND ([Extent1].[CentraleProduit] = [Extent2].[CentraleProduit])
		)) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT 
			1 AS [C1]
			FROM [dbo].[Ventes] AS [Extent3]
			WHERE ([Extent1].[LaboratoireCode] = [Extent3].[LaboratoireCode]) AND ([Extent1].[CentraleCode] = [Extent3].[CentraleCode]) AND ([Extent1].[CentraleProduit] = [Extent3].[CentraleProduit])
		)) THEN cast(0 as bit) END AS [C1]
		FROM [dbo].[ProduitsCentrales] AS [Extent1]
		WHERE ([Extent1].[IDPack] IS NOT NULL) AND (1000 = [Extent1].[IDPack])
	)  AS [Project3]
	WHERE 1 = [Project3].[C1]
)) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT 
	1 AS [C1]
	FROM ( SELECT 
		CASE WHEN ( EXISTS (SELECT 
			1 AS [C1]
			FROM [dbo].[Ventes] AS [Extent5]
			WHERE ([Extent4].[LaboratoireCode] = [Extent5].[LaboratoireCode]) AND ([Extent4].[CentraleCode] = [Extent5].[CentraleCode]) AND ([Extent4].[CentraleProduit] = [Extent5].[CentraleProduit])
		)) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT 
			1 AS [C1]
			FROM [dbo].[Ventes] AS [Extent6]
			WHERE ([Extent4].[LaboratoireCode] = [Extent6].[LaboratoireCode]) AND ([Extent4].[CentraleCode] = [Extent6].[CentraleCode]) AND ([Extent4].[CentraleProduit] = [Extent6].[CentraleProduit])
		)) THEN cast(0 as bit) END AS [C1]
		FROM [dbo].[ProduitsCentrales] AS [Extent4]
		WHERE ([Extent4].[IDPack] IS NOT NULL) AND (1000 = [Extent4].[IDPack])
	)  AS [Project7]
	WHERE 1 = [Project7].[C1]
)) THEN cast(0 as bit) END AS [C1]
FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]

This query will return only 1 row to the client

该查询将仅向客户端返回1行

This new version of the query will resolve our 1st version issue.

此新版本的查询将解决我们的第1版问题。

But as I keep the same approach from version 1, I need create 2 queries:

但是，由于我与版本1保持相同的方法，因此我需要创建2个查询：

One to get information about sales existence for each SenderPack of a Pack...

一个用于获取有关每个Pack的SenderPack销售状况的信息...

This does the same work as Version 1, but on the server side.

这与版本1相同，但在服务器端。

查询的第3版 (3th Version of the query)

dim result as boolean
result = (From s In Sales
			Where s.SenderPack.Pack.PKID = 1000
			).Any
					  

Result.dump

Sql version:

SQL版本：

SELECT 
CASE WHEN ( EXISTS (SELECT 
	1 AS [C1]
	FROM  [dbo].[Ventes] AS [Extent1]
	INNER JOIN [dbo].[ProduitsCentrales] AS [Extent2] ON ([Extent1].[LaboratoireCode] = [Extent2].[LaboratoireCode]) AND ([Extent1].[CentraleCode] = [Extent2].[CentraleCode]) AND ([Extent1].[CentraleProduit] = [Extent2].[CentraleProduit])
	WHERE 1000 = [Extent2].[IDPack]
)) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT 
	1 AS [C1]
	FROM  [dbo].[Ventes] AS [Extent3]
	INNER JOIN [dbo].[ProduitsCentrales] AS [Extent4] ON ([Extent3].[LaboratoireCode] = [Extent4].[LaboratoireCode]) AND ([Extent3].[CentraleCode] = [Extent4].[CentraleCode]) AND ([Extent3].[CentraleProduit] = [Extent4].[CentraleProduit])
	WHERE 1000 = [Extent4].[IDPack]
)) THEN cast(0 as bit) END AS [C1]
FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]