<

Optimizing Entity Framework Linq query

Published on
9,149 Points
2,949 Views
2 Endorsements
Last Modified:
Approved

Introduction


Using Linq and Entity Framework may result in very slow operations.
Our 1st reflection might be: "To bad EF is too slow and can't be used in production applications."
But, we may wish to consider rechecking our code…

Background 

In one of my application, users had experienced a very long operation (some times more than 1 minute), which is unacceptable for a production application.
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.
I use LinqPad application, too, to execute my Linq query easily.

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 : 

Dim result As Boolean

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

result.dump

Open in new window


Execution time :  24.512s

As we can see 24 sec just to rest a Boolean, it certainly to long.
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)

Open in new window

This query return 330 rows.
1st query
The issue is:
As we use "packEntity.SenderPacks", EF will load all "packEntity.SenderPacks" entities and after query for each entity "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

Open in new window


Execution time 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]

Open in new window


This query will return only 1 row to the client

2nd version of the query
This new version of the query will resolve our 1st version issue.
But as I keep the same approach from version 1, I need create 2 queries:
One to get information about sales existence for each SenderPack of a Pack...

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

3th Version of the query


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

Result.dump

Open in new window


Sql version:
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]

Open in new window

Excecution time : 0.016s
Version 3 of the query
In this version I change my approach and start from sales.
The execution time is the same but by this way I have only 1 query.

The Linq code is easier to read, and the SQL's execution plan is better.


Conclusion

Working With EF and Linq is not that simple if we are worried about performance issues.
The good news is, we can use existing tools to analyze our query and have a chance to optimize it.
2
Comment
0 Comments

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Join & Write a Comment

Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
See the Basics of Office 365's Note Taking app, OneNote
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month