Open
Description
openedon Feb 7, 2023
Description
We have in our production a linq query that took about 3 seconds to compile with north of 250 entities to query. We recently switched from EF6 to EFC7 and this query now takes about 3 minutes. We have produced a new query that works better for EF Core but we'd like to get your input to why.
We have two thoughts:
- Are we simply running to many queries towards our database/entity?
- The query is running COALESCE in a complex query for EF Core a lot, making the it run it twice - could this be a issue?
At this time, we don't have a small reproducible code snippet/project, but if needed we could see if we can provide it.
Include your code
The query/linq is the following:
var result = (
from account in bankAccounts
where account.Owner.CompanyUsages.HasFlag(CompanyUsages.LegalEntity)
let knownBalance = entities.BankBalances
// Look for all statuses except for Calculated, i.e. all balanceposts stored in database
.Where(bb => (bb.Status != BalanceStatus.Calculated)
&& bb.AccountId == account.Id
&& bb.BankBalanceDate <= truncatedBalanceDate)
.OrderByDescending(bb => bb.BankBalanceDate)
.FirstOrDefault()
let diff = entities.Flows
.Where(flow => (knownBalance == null || (knownBalance.BankBalanceDate < flow.Time))
&& flow.Time <= truncatedBalanceDate
&& flow.EntityBankAccountId == account.Id
&& flow.Transaction.Status != StatusEnum.Cancelled)
.Sum(flow => (decimal?)flow.Amount)
select new BankBalanceAccount
{
Account = account,
AccountOwner = account.Owner,
AccountBank = account.Bank,
BankBalance = knownBalance,
Status = (knownBalance.BankBalanceDate == truncatedBalanceDate) ? knownBalance.Status : BalanceStatus.Calculated,
AvailableBalance = (knownBalance != null ? knownBalance.AvailableBalance : 0m) + (diff ?? 0m),
BookedBalance = (knownBalance != null ? knownBalance.BookedBalance : 0m) + (diff ?? 0m)
});The query in EF6 produces the follow SQL:
SELECT
[Project4].[Id] AS [Id],
[Project4].[C1] AS [C1],
[Project4].[OwnerId] AS [OwnerId],
[Project4].[Name] AS [Name],
[Project4].[Description] AS [Description],
[Project4].[AccountNo] AS [AccountNo],
[Project4].[AssetId] AS [AssetId],
[Project4].[BankId] AS [BankId],
[Project4].[ReconciliateActive] AS [ReconciliateActive],
[Project4].[ReconciliationBeginDate] AS [ReconciliationBeginDate],
[Project4].[IsActive] AS [IsActive],
[Project4].[Iban] AS [Iban],
[Project4].[SortCode] AS [SortCode],
[Project4].[CorrespondentBIC] AS [CorrespondentBIC],
[Project4].[BIC] AS [BIC],
[Project4].[CashPoolId] AS [CashPoolId],
[Project4].[AccountOrigin] AS [AccountOrigin],
[Project4].[ClearingInstitute] AS [ClearingInstitute],
[Project4].[IsTopAccount] AS [IsTopAccount],
[Project4].[AbsoluteTopAccountId] AS [AbsoluteTopAccountId],
[Project4].[CashPoolType] AS [CashPoolType],
[Project4].[BankAccountInterestDealPartId] AS [BankAccountInterestDealPartId],
[Project4].[NomentiaId] AS [NomentiaId],
[Project4].[NomentiaBankConnectionPoint] AS [NomentiaBankConnectionPoint],
[Extent6].[Id] AS [Id1],
[Extent6].[Name] AS [Name1],
[Extent6].[Description] AS [Description1],
[Extent6].[Email] AS [Email],
[Extent6].[OrganizationNumber] AS [OrganizationNumber],
[Extent6].[BookingCurrencyId] AS [BookingCurrencyId],
[Extent6].[BIC] AS [BIC1],
[Extent6].[CompanyType] AS [CompanyType],
[Extent6].[Address_Street] AS [Address_Street],
[Extent6].[Address_City] AS [Address_City],
[Extent6].[Address_PostalCode] AS [Address_PostalCode],
[Extent6].[CompanyUsages] AS [CompanyUsages],
[Extent6].[LegalName] AS [LegalName],
[Extent6].[CountryCode] AS [CountryCode],
[Extent6].[AlternativeLegalName] AS [AlternativeLegalName],
[Extent6].[IsActive] AS [IsActive1],
[Extent6].[NomentiaId] AS [NomentiaId1],
[Extent6].[NomentiaCompanyCode] AS [NomentiaCompanyCode],
[Extent6].[NomentiaBankConnectionPoint] AS [NomentiaBankConnectionPoint1],
[Extent7].[Id] AS [Id2],
[Extent7].[Name] AS [Name2],
[Extent7].[Description] AS [Description2],
[Extent7].[Email] AS [Email1],
[Extent7].[OrganizationNumber] AS [OrganizationNumber1],
[Extent7].[BookingCurrencyId] AS [BookingCurrencyId1],
[Extent7].[BIC] AS [BIC2],
[Extent7].[CompanyType] AS [CompanyType1],
[Extent7].[Address_Street] AS [Address_Street1],
[Extent7].[Address_City] AS [Address_City1],
[Extent7].[Address_PostalCode] AS [Address_PostalCode1],
[Extent7].[CompanyUsages] AS [CompanyUsages1],
[Extent7].[LegalName] AS [LegalName1],
[Extent7].[CountryCode] AS [CountryCode1],
[Extent7].[AlternativeLegalName] AS [AlternativeLegalName1],
[Extent7].[IsActive] AS [IsActive2],
[Extent7].[NomentiaId] AS [NomentiaId2],
[Extent7].[NomentiaCompanyCode] AS [NomentiaCompanyCode1],
[Extent7].[NomentiaBankConnectionPoint] AS [NomentiaBankConnectionPoint2],
[Project4].[BankBalanceDate] AS [BankBalanceDate],
[Project4].[AccountId] AS [AccountId],
[Project4].[AvailableBalance] AS [AvailableBalance],
[Project4].[BookedBalance] AS [BookedBalance],
[Project4].[Status] AS [Status],
CASE WHEN ([Project4].[BankBalanceDate] = @p__linq__2) THEN [Project4].[Status] ELSE 0 END AS [C2],
(CASE WHEN ( NOT (([Project4].[BankBalanceDate] IS NULL) AND ([Project4].[AccountId] IS NULL))) THEN [Project4].[AvailableBalance] ELSE cast(0 as decimal(18)) END) + (CASE WHEN ([Project4].[C2] IS NULL) THEN cast(0 as decimal(18)) ELSE [Project4].[C2] END) AS [C3],
(CASE WHEN ( NOT (([Project4].[BankBalanceDate] IS NULL) AND ([Project4].[AccountId] IS NULL))) THEN [Project4].[BookedBalance] ELSE cast(0 as decimal(18)) END) + (CASE WHEN ([Project4].[C2] IS NULL) THEN cast(0 as decimal(18)) ELSE [Project4].[C2] END) AS [C4]
FROM (SELECT
[Project3].[Id] AS [Id],
[Project3].[OwnerId] AS [OwnerId],
[Project3].[Name] AS [Name],
[Project3].[Description] AS [Description],
[Project3].[AccountNo] AS [AccountNo],
[Project3].[AssetId] AS [AssetId],
[Project3].[BankId] AS [BankId],
[Project3].[ReconciliateActive] AS [ReconciliateActive],
[Project3].[ReconciliationBeginDate] AS [ReconciliationBeginDate],
[Project3].[IsActive] AS [IsActive],
[Project3].[Iban] AS [Iban],
[Project3].[SortCode] AS [SortCode],
[Project3].[CorrespondentBIC] AS [CorrespondentBIC],
[Project3].[BIC] AS [BIC],
[Project3].[CashPoolId] AS [CashPoolId],
[Project3].[AccountOrigin] AS [AccountOrigin],
[Project3].[ClearingInstitute] AS [ClearingInstitute],
[Project3].[IsTopAccount] AS [IsTopAccount],
[Project3].[AbsoluteTopAccountId] AS [AbsoluteTopAccountId],
[Project3].[CashPoolType] AS [CashPoolType],
[Project3].[BankAccountInterestDealPartId] AS [BankAccountInterestDealPartId],
[Project3].[NomentiaId] AS [NomentiaId],
[Project3].[NomentiaBankConnectionPoint] AS [NomentiaBankConnectionPoint],
[Project3].[C1] AS [C1],
[Project3].[BankBalanceDate] AS [BankBalanceDate],
[Project3].[AccountId] AS [AccountId],
[Project3].[AvailableBalance] AS [AvailableBalance],
[Project3].[BookedBalance] AS [BookedBalance],
[Project3].[Status] AS [Status],
(SELECT
SUM([Extent4].[Amount]) AS [A1]
FROM [dbo].[Flows] AS [Extent4]
LEFT OUTER JOIN [dbo].[Transactions] AS [Extent5] ON ([Extent5].[Discriminator] IN (4,5,15,6,1,3,14,10,9,2,16,8,7,12,11,13,17)) AND ([Extent4].[TransactionId] = [Extent5].[Id])
WHERE ((([Project3].[BankBalanceDate] IS NULL) AND ([Project3].[AccountId] IS NULL)) OR ([Project3].[BankBalanceDate] < [Extent4].[Time])) AND ([Extent4].[Time] <= @p__linq__1) AND ([Extent4].[EntityBankAccountId] = [Project3].[Id]) AND ( NOT ((16777216 = [Extent5].[Status]) AND ([Extent5].[Status] IS NOT NULL)))) AS [C2]
FROM ( SELECT
[Project1].[Id] AS [Id],
[Project1].[OwnerId] AS [OwnerId],
[Project1].[Name] AS [Name],
[Project1].[Description] AS [Description],
[Project1].[AccountNo] AS [AccountNo],
[Project1].[AssetId] AS [AssetId],
[Project1].[BankId] AS [BankId],
[Project1].[ReconciliateActive] AS [ReconciliateActive],
[Project1].[ReconciliationBeginDate] AS [ReconciliationBeginDate],
[Project1].[IsActive] AS [IsActive],
[Project1].[Iban] AS [Iban],
[Project1].[SortCode] AS [SortCode],
[Project1].[CorrespondentBIC] AS [CorrespondentBIC],
[Project1].[BIC] AS [BIC],
[Project1].[CashPoolId] AS [CashPoolId],
[Project1].[AccountOrigin] AS [AccountOrigin],
[Project1].[ClearingInstitute] AS [ClearingInstitute],
[Project1].[IsTopAccount] AS [IsTopAccount],
[Project1].[AbsoluteTopAccountId] AS [AbsoluteTopAccountId],
[Project1].[CashPoolType] AS [CashPoolType],
[Project1].[BankAccountInterestDealPartId] AS [BankAccountInterestDealPartId],
[Project1].[NomentiaId] AS [NomentiaId],
[Project1].[NomentiaBankConnectionPoint] AS [NomentiaBankConnectionPoint],
[Project1].[C1] AS [C1],
[Limit1].[BankBalanceDate] AS [BankBalanceDate],
[Limit1].[AccountId] AS [AccountId],
[Limit1].[AvailableBalance] AS [AvailableBalance],
[Limit1].[BookedBalance] AS [BookedBalance],
[Limit1].[Status] AS [Status]
FROM (SELECT
[Extent1].[Id] AS [Id],
[Extent1].[OwnerId] AS [OwnerId],
[Extent1].[Name] AS [Name],
[Extent1].[Description] AS [Description],
[Extent1].[AccountNo] AS [AccountNo],
[Extent1].[AssetId] AS [AssetId],
[Extent1].[BankId] AS [BankId],
[Extent1].[ReconciliateActive] AS [ReconciliateActive],
[Extent1].[ReconciliationBeginDate] AS [ReconciliationBeginDate],
[Extent1].[IsActive] AS [IsActive],
[Extent1].[Iban] AS [Iban],
[Extent1].[SortCode] AS [SortCode],
[Extent1].[CorrespondentBIC] AS [CorrespondentBIC],
[Extent1].[BIC] AS [BIC],
[Extent1].[CashPoolId] AS [CashPoolId],
[Extent1].[AccountOrigin] AS [AccountOrigin],
[Extent1].[ClearingInstitute] AS [ClearingInstitute],
[Extent1].[IsTopAccount] AS [IsTopAccount],
[Extent1].[AbsoluteTopAccountId] AS [AbsoluteTopAccountId],
[Extent1].[CashPoolType] AS [CashPoolType],
[Extent1].[BankAccountInterestDealPartId] AS [BankAccountInterestDealPartId],
[Extent1].[NomentiaId] AS [NomentiaId],
[Extent1].[NomentiaBankConnectionPoint] AS [NomentiaBankConnectionPoint],
'0X0X' AS [C1]
FROM [dbo].[Accounts] AS [Extent1]
INNER JOIN [dbo].[Companies] AS [Extent2] ON [Extent1].[OwnerId] = [Extent2].[Id]
WHERE ([Extent1].[Discriminator] = N'BANK') AND ((( CAST( [Extent2].[CompanyUsages] AS tinyint)) & ( CAST( cast(8 as tinyint) AS tinyint))) = 8) ) AS [Project1]
OUTER APPLY (SELECT TOP (1) [Project2].[BankBalanceDate] AS [BankBalanceDate], [Project2].[AccountId] AS [AccountId], [Project2].[AvailableBalance] AS [AvailableBalance], [Project2].[BookedBalance] AS [BookedBalance], [Project2].[Status] AS [Status]
FROM ( SELECT
[Extent3].[BankBalanceDate] AS [BankBalanceDate],
[Extent3].[AccountId] AS [AccountId],
[Extent3].[AvailableBalance] AS [AvailableBalance],
[Extent3].[BookedBalance] AS [BookedBalance],
[Extent3].[Status] AS [Status]
FROM [dbo].[BankBalances] AS [Extent3]
WHERE (0 <> [Extent3].[Status]) AND ([Extent3].[AccountId] = [Project1].[Id]) AND ([Extent3].[BankBalanceDate] <= @p__linq__0)
) AS [Project2]
ORDER BY [Project2].[BankBalanceDate] DESC ) AS [Limit1]
) AS [Project3] ) AS [Project4]
LEFT OUTER JOIN [dbo].[Companies] AS [Extent6] ON [Project4].[OwnerId] = [Extent6].[Id]
LEFT OUTER JOIN [dbo].[Companies] AS [Extent7] ON [Project4].[BankId] = [Extent7].[Id]EF Core produces the following SQL:
-- Region Parameters
-- @__truncatedBalanceDate_0='2023-02-06T00:00:00.0000000' (DbType = Date)
-- EndRegion
SELECT [a].[Id], [a].[AccountNo], [a].[AssetId], [a].[BankId], [a].[Description], [a].[Discriminator], [a].[IsActive], [a].[Name], [a].[OwnerId], [a].[ReconciliateActive], [a].[ReconciliationBeginDate], [a].[AbsoluteTopAccountId], [a].[AccountOrigin], [a].[BIC], [a].[BankAccountInterestDealPartId], [a].[CashPoolId], [a].[CashPoolType], [a].[ClearingInstitute], [a].[CorrespondentBIC], [a].[Iban], [a].[IsTopAccount], [a].[NomentiaBankConnectionPoint], [a].[NomentiaId], [a].[SortCode], [c].[Id], [c].[AlternativeLegalName], [c].[BIC], [c].[BookingCurrencyId], [c].[Address_City], [c].[CompanyType], [c].[CompanyUsages], [c].[CountryCode], [c].[Description], [c].[Email], [c].[IsActive], [c].[LegalName], [c].[Name], [c].[NomentiaBankConnectionPoint], [c].[NomentiaCompanyCode], [c].[NomentiaId], [c].[OrganizationNumber], [c].[Address_PostalCode], [c].[Address_Street], [c0].[Id], [c0].[AlternativeLegalName], [c0].[BIC], [c0].[BookingCurrencyId], [c0].[Address_City], [c0].[CompanyType], [c0].[CompanyUsages], [c0].[CountryCode], [c0].[Description], [c0].[Email], [c0].[IsActive], [c0].[LegalName], [c0].[Name], [c0].[NomentiaBankConnectionPoint], [c0].[NomentiaCompanyCode], [c0].[NomentiaId], [c0].[OrganizationNumber], [c0].[Address_PostalCode], [c0].[Address_Street], CASE
WHEN (
SELECT TOP(1) [b0].[BankBalanceDate]
FROM [dbo].[BankBalances] AS [b0]
WHERE [b0].[Status] <> 0 AND [b0].[AccountId] = [a].[Id] AND [b0].[BankBalanceDate] <= @__truncatedBalanceDate_0
ORDER BY [b0].[BankBalanceDate] DESC) = @__truncatedBalanceDate_0 THEN (
SELECT TOP(1) [b1].[Status]
FROM [dbo].[BankBalances] AS [b1]
WHERE [b1].[Status] <> 0 AND [b1].[AccountId] = [a].[Id] AND [b1].[BankBalanceDate] <= @__truncatedBalanceDate_0
ORDER BY [b1].[BankBalanceDate] DESC)
ELSE 0
END, CASE
WHEN EXISTS (
SELECT 1
FROM [dbo].[BankBalances] AS [b2]
WHERE [b2].[Status] <> 0 AND [b2].[AccountId] = [a].[Id] AND [b2].[BankBalanceDate] <= @__truncatedBalanceDate_0) THEN (
SELECT TOP(1) [b3].[AvailableBalance]
FROM [dbo].[BankBalances] AS [b3]
WHERE [b3].[Status] <> 0 AND [b3].[AccountId] = [a].[Id] AND [b3].[BankBalanceDate] <= @__truncatedBalanceDate_0
ORDER BY [b3].[BankBalanceDate] DESC)
ELSE 0.0
END + COALESCE((
SELECT COALESCE(SUM([f].[Amount]), 0.0)
FROM [dbo].[Flows] AS [f]
INNER JOIN [dbo].[Transactions] AS [t1] ON [f].[TransactionId] = [t1].[Id]
WHERE (NOT (EXISTS (
SELECT 1
FROM [dbo].[BankBalances] AS [b4]
WHERE [b4].[Status] <> 0 AND [b4].[AccountId] = [a].[Id] AND [b4].[BankBalanceDate] <= @__truncatedBalanceDate_0)) OR (
SELECT TOP(1) [b5].[BankBalanceDate]
FROM [dbo].[BankBalances] AS [b5]
WHERE [b5].[Status] <> 0 AND [b5].[AccountId] = [a].[Id] AND [b5].[BankBalanceDate] <= @__truncatedBalanceDate_0
ORDER BY [b5].[BankBalanceDate] DESC) < [f].[Time]) AND [f].[Time] <= @__truncatedBalanceDate_0 AND [f].[EntityBankAccountId] = [a].[Id] AND [t1].[Status] <> 16777216), 0.0), CASE
WHEN EXISTS (
SELECT 1
FROM [dbo].[BankBalances] AS [b6]
WHERE [b6].[Status] <> 0 AND [b6].[AccountId] = [a].[Id] AND [b6].[BankBalanceDate] <= @__truncatedBalanceDate_0) THEN (
SELECT TOP(1) [b7].[BookedBalance]
FROM [dbo].[BankBalances] AS [b7]
WHERE [b7].[Status] <> 0 AND [b7].[AccountId] = [a].[Id] AND [b7].[BankBalanceDate] <= @__truncatedBalanceDate_0
ORDER BY [b7].[BankBalanceDate] DESC)
ELSE 0.0
END + COALESCE((
SELECT COALESCE(SUM([f0].[Amount]), 0.0)
FROM [dbo].[Flows] AS [f0]
INNER JOIN [dbo].[Transactions] AS [t2] ON [f0].[TransactionId] = [t2].[Id]
WHERE (NOT (EXISTS (
SELECT 1
FROM [dbo].[BankBalances] AS [b8]
WHERE [b8].[Status] <> 0 AND [b8].[AccountId] = [a].[Id] AND [b8].[BankBalanceDate] <= @__truncatedBalanceDate_0)) OR (
SELECT TOP(1) [b9].[BankBalanceDate]
FROM [dbo].[BankBalances] AS [b9]
WHERE [b9].[Status] <> 0 AND [b9].[AccountId] = [a].[Id] AND [b9].[BankBalanceDate] <= @__truncatedBalanceDate_0
ORDER BY [b9].[BankBalanceDate] DESC) < [f0].[Time]) AND [f0].[Time] <= @__truncatedBalanceDate_0 AND [f0].[EntityBankAccountId] = [a].[Id] AND [t2].[Status] <> 16777216), 0.0), [t0].[BankBalanceDate], [t0].[AccountId], [t0].[AvailableBalance], [t0].[BookedBalance], [t0].[Status]
FROM [dbo].[Accounts] AS [a]
INNER JOIN [dbo].[Companies] AS [c] ON [a].[OwnerId] = [c].[Id]
INNER JOIN [dbo].[Companies] AS [c0] ON [a].[BankId] = [c0].[Id]
LEFT JOIN (
SELECT [t].[BankBalanceDate], [t].[AccountId], [t].[AvailableBalance], [t].[BookedBalance], [t].[Status]
FROM (
SELECT [b].[BankBalanceDate], [b].[AccountId], [b].[AvailableBalance], [b].[BookedBalance], [b].[Status], ROW_NUMBER() OVER(PARTITION BY [b].[AccountId] ORDER BY [b].[BankBalanceDate] DESC) AS [row]
FROM [dbo].[BankBalances] AS [b]
WHERE [b].[Status] <> 0 AND [b].[BankBalanceDate] <= @__truncatedBalanceDate_0
) AS [t]
WHERE [t].[row] <= 1
) AS [t0] ON [a].[Id] = [t0].[AccountId]
WHERE ([c].[CompanyUsages] & CAST(8 AS tinyint)) = CAST(8 AS tinyint)
GOThis is the linq that we used instead of the other one:
var result = (
from account in BankAccounts
where account.Owner.CompanyUsages.HasFlag(CompanyUsages.LegalEntity)
join bal in BankBalances
on account.Id equals bal.AccountId into balGroup
from knownBalance in balGroup.DefaultIfEmpty()
where knownBalance.BankBalanceDate == BankBalances.Where(bb2 => bb2.AccountId == knownBalance.AccountId && bb2.BankBalanceDate <= truncatedBalanceDate).Max(a => a.BankBalanceDate)
let diff = Flows
.Where(flow => (knownBalance == null || (knownBalance.BankBalanceDate < flow.Time))
&& flow.Time <= truncatedBalanceDate
&& flow.EntityBankAccountId == account.Id
// Exclude cancelled transactions
&& flow.Transaction.Status != StatusEnum.Cancelled)
.Sum(flow => flow.Amount)
select new CRM.Finance.Web.Subsystems.BalanceSystem.BankBalanceAccount
{
Account = account,
AccountOwner = account.Owner,
AccountBank = account.Bank,
BankBalance = knownBalance,
Status = (knownBalance.BankBalanceDate == truncatedBalanceDate) ? knownBalance.Status : BalanceStatus.Calculated,
AvailableBalance = (knownBalance != null ? knownBalance.AvailableBalance : 0m) + diff,
BookedBalance = (knownBalance != null ? knownBalance.BookedBalance : 0m) + diff
});Include provider and version information
EF Core version:
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7.0
Using Entity Framework Core 7.0.0
Operating system: WIndows 10/11
IDE: Visual Studio 2022 17.4
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment