Consider removing identifier orderings when doing identity resolution

Open
#24,377 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
5/5
Estimated time
Over a week
Newbie friendliness
25/100
Issue type
Refactor
Clarity
Needs clarification
Activity status
Stale
Tech stack
csharp, sql
Domain
databases, performance

Research direction

Start by tracing related-entity loading, identity resolution, and the shaper described in the issue, then compare generated SQL with and without identifier orderings. Measure server-side ordering costs against the shaper's local dictionary lookups, and consider the work done only when the performance trade-off and affected query behavior are demonstrated.

Written by the indexing model from the issue text.

Description

area-perf area-query needs-design

When loading related entities, we currently generate orderings to group together rows for the same entity. This is necessary for queries without identity resolution, since the shaper detects that new entities by the change in identifier. However, queries with identity resolution perform local lookup anyway, so these orderings aren't needed.

For example, consider a Blog with two collection navigations, Posts and Authors. When loading this entire graph, we currently generate:

SELECT [b].[Id], [b].[Name], [p].[Id], [p].[BlogId], [a].[Id], [a].[BlogId]
FROM [Blogs] AS [b]
LEFT JOIN [Post] AS [p] ON [b].[Id] = [p].[BlogId]
LEFT JOIN [Author] AS [a] ON [b].[Id] = [a].[BlogId]
ORDER BY [b].[Id], [p].[Id], [a].[Id]

Queries using identity resolution could omit the last two orderings.

This would potentially imply more local dictionary lookups, as rows are returned unordered and the shaper needs to jump back and forth between instances. My gut feeling is that the server-side ordering is much more expensive, but we'd have to confirm this first.

/cc @smitpatel

Dominant language
C#
Stars
14.8k
Forks
3.4k
Avg merge
2d 5h
Merged PRs (30d)
134

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from dotnet/efcore

All issues in dotnet/efcore

Similar issues

More C# issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.