Browse All Articles > Optimizing Entity Framework Linq query
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.dumpExecution 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)
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.dumpExecution 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]This query will return only 1 row to the client
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.dumpSql 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]
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.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)