SQL query with Entity Framework high logical reads

Question

I have this code which uses Entity Framework to query SQL Server in my .NET 9 project:

vm.LastMatch = _context.Matches
    .Include(i => i.HomeTeam)
    .ThenInclude(i => i.Venue)
    .Include(i => i.AwayTeam)
    .ThenInclude(i => i.Venue)
    .OrderByDescending(i => i.DatePlayed)
    .Where(i => i.MatchStatus == MatchStatus.Completed)
    .Where(i => i.HomeTeam.Id == vm.SeasonTeam.Id || i.AwayTeam.Id == vm.SeasonTeam.Id)
    .FirstOrDefault();

This is the resulting SQL query that it generates:

SELECT TOP(1) [m].[Id], [m].[AdvanceWithoutResult], [m].[AwayAggregateScore], [m].[AwayBallColour], [m].[AwayCurrentSectionScore], [m].[AwayDeciderScore], [m].[AwayEntryId], [m].[AwayExtension], [m].[AwayHandicap], [m].[AwayNote], [m].[AwayParentMatchNo], [m].[AwayParentWinnerOrLoser], [m].[AwayPlayerOfTheMatchId], [m].[AwayPreviousPosition], [m].[AwayScore], [m].[AwaySetScore], [m].[AwayTeamId], [m].[BlockPlayerScheduling], [m].[CalendarDateId], [m].[ChallengeMatch], [m].[DatePlayed], [m].[Defeats], [m].[DependentMatchId], [m].[Disputed], [m].[Friendly], [m].[GroupId], [m].[HomeAggregateScore], [m].[HomeBallColour], [m].[HomeCurrentSectionScore], [m].[HomeDeciderScore], [m].[HomeEntryId], [m].[HomeExtension], [m].[HomeHandicap], [m].[HomeNote], [m].[HomeParentMatchNo], [m].[HomeParentWinnerOrLoser], [m].[HomePlayerOfTheMatchId], [m].[HomePreviousPosition], [m].[HomeScore], [m].[HomeSetScore], [m].[HomeTeamId], [m].[LastUpdated], [m].[LeagueId], [m].[LegNumber], [m].[LegsId], [m].[MatchCode], [m].[MatchFormatId], [m].[MatchNumber], [m].[MatchStatus], [m].[MatchType], [m].[MatchdayNumber], [m].[MiniKnockoutId], [m].[MiniKnockoutRoundId], [m].[OldCompetitionMatchId], [m].[Referee1Id], [m].[Referee2Id], [m].[ResultExists], [m].[RoundId], [m].[Scheduled], [m].[ScheduledDateTime], [m].[ScoreboardMatch], [m].[SeasonCompetitionId], [m].[SeasonDivisionId], [m].[SeasonId], [m].[SixRedShooter], [m].[SubmitStatus], [m].[TableId], [m].[TimeCompleted], [m].[TimeStarted], [m].[TwoLegs], [m].[VenueBookingIdentifier], [m].[VenueBookingNote], [m].[VenueBookingStatus], [m].[VenueId], [m].[Walkover], [s].[Id], [s].[ApprovedById], [s].[ApprovedDateTime], [s].[AvatarFileName], [s].[CaptainId], [s].[CookieIdentifier], [s].[CreatedById], [s].[CreatedDate], [s].[CustomField1Value], [s].[CustomField2Value], [s].[CustomField3Value], [s].[CustomField4Value], [s].[CustomField5Value], [s].[Handicap], [s].[InvoiceAmount], [s].[InvoiceId], [s].[Invoiced], [s].[IsNew], [s].[LeagueDay], [s].[LeagueId], [s].[Legacy], [s].[NewTeamProgress], [s].[Number], [s].[OriginalEntry], [s].[Paid], [s].[PaidAmount], [s].[RegistrationStatus], [s].[SeasonDivisionId], [s].[SeasonId], [s].[SeasonStatus], [s].[Subdivision], [s].[TableId], [s].[TeamId], [s].[TeamName], [s].[VenueId], [s].[ViceCaptainId], [v].[Id], [v].[Active], [v].[Address1], [v].[Address2], [v].[BookingApprovals], [v].[ContactName], [v].[Country], [v].[County], [v].[CreatedDate], [v].[Description], [v].[HidePrimarySponsor], [v].[LeagueId], [v].[LogoFileName], [v].[Name], [v].[PhoneNumber], [v].[PostCode], [v].[Shared], [v].[Town], [v].[URL], [s0].[Id], [s0].[ApprovedById], [s0].[ApprovedDateTime], [s0].[AvatarFileName], [s0].[CaptainId], [s0].[CookieIdentifier], [s0].[CreatedById], [s0].[CreatedDate], [s0].[CustomField1Value], [s0].[CustomField2Value], [s0].[CustomField3Value], [s0].[CustomField4Value], [s0].[CustomField5Value], [s0].[Handicap], [s0].[InvoiceAmount], [s0].[InvoiceId], [s0].[Invoiced], [s0].[IsNew], [s0].[LeagueDay], [s0].[LeagueId], [s0].[Legacy], [s0].[NewTeamProgress], [s0].[Number], [s0].[OriginalEntry], [s0].[Paid], [s0].[PaidAmount], [s0].[RegistrationStatus], [s0].[SeasonDivisionId], [s0].[SeasonId], [s0].[SeasonStatus], [s0].[Subdivision], [s0].[TableId], [s0].[TeamId], [s0].[TeamName], [s0].[VenueId], [s0].[ViceCaptainId], [v0].[Id], [v0].[Active], [v0].[Address1], [v0].[Address2], [v0].[BookingApprovals], [v0].[ContactName], [v0].[Country], [v0].[County], [v0].[CreatedDate], [v0].[Description], [v0].[HidePrimarySponsor], [v0].[LeagueId], [v0].[LogoFileName], [v0].[Name], [v0].[PhoneNumber], [v0].[PostCode], [v0].[Shared], [v0].[Town], [v0].[URL]
FROM [Matches] AS [m]
LEFT JOIN [SeasonTeams] AS [s] ON [m].[HomeTeamId] = [s].[Id]
LEFT JOIN [SeasonTeams] AS [s0] ON [m].[AwayTeamId] = [s0].[Id]
LEFT JOIN [Venues] AS [v] ON [s].[VenueId] = [v].[Id]
LEFT JOIN [Venues] AS [v0] ON [s0].[VenueId] = [v0].[Id]
WHERE [m].[MatchStatus] = 3 AND ([s].[Id] = 42160 OR [s0].[Id]  = 42160)

And on reviewing it in the query store, it has something like 2.5 million logical reads for this single query.

This seems quite high. I believe improving things like this helps with overall query performance.

Can anyone advise why this is so high and what can be done to resolve it?

Indexes on the tables are mainly those generated by default by Entity Framework for the related tables:

For query performance questions, we need at a minimum: the table and index definitions (in full, like CREATE TABLE, and not as a screenshot), and the query plan (you can share via brentozar.com/pastetheplan). We're not going to be able to answer properly otherwise. — Charlieface
– Charlieface, Commented May 7 at 17:21

Charlieface · Accepted Answer · 2025-05-07 17:33:02Z

Some things you can do to improve performance:

Use AsSplitQuery, which will prevent the generated SQL turning into a giant cross-join.
Split the || into a Concat, which will get you a UNION ALL query.

var baseQuery = _context.Matches
    .Include(i => i.HomeTeam)
    .ThenInclude(i => i.Venue)
    .Include(i => i.AwayTeam)
    .ThenInclude(i => i.Venue)
    .Where(i => i.MatchStatus == MatchStatus.Completed)
    .AsSplitQuery();

vm.LastMatch = baseQuery.Where(i => i.HomeTeam.Id == vm.SeasonTeam.Id)
    .Concat(baseQuery.Where(i => i.AwayTeam.Id == vm.SeasonTeam.Id))
    .OrderByDescending(i => i.DatePlayed)
    .FirstOrDefault();

Furthermore, the indexing probably needs to be improved, as lots of single-column indexes is actually counter-productive. I would expect the following indexes for this query (note the INCLUDE columns)

Matches (AwayTeamId, MatchStatus, DatePlayed DESC) INCLUDE (HomeTeamId, Other_Columns_Here)
Matches (HomeTeamId, MatchStatus, DatePlayed DESC) INCLUDE (AwayTeamId, Other_Columns_Here)

If you can, use a DTO class which limits the number of columns you need to query. Then you can remove them from the INCLUDE.

Collectives™ on Stack Overflow

SQL query with Entity Framework high logical reads

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related