spikerj/spikersoft-issues
1
0
Fork 0
Code Issues 196 Pull Requests Actions Packages Projects Releases Wiki Activity

[High] Activity/knowledge query handlers load entire UserProfiles/UserActivities tables into memory #60

New Issue
Closed
opened 2026-05-05 04:15:29 +00:00 by spikerj · 1 comment
spikerj commented 2026-05-05 04:15:29 +00:00
Owner
Copy Link
Copy Source

Severity: High (perf/scalability)

Files:

  • SpikerSoft.Business/Domain/Activity/Queries/GetChildKnowledgeProgress/GetChildKnowledgeProgressQueryHandler.cs (~16) -- UserProfiles.ToListAsync() to find one user
  • SpikerSoft.Business/Domain/Activity/Queries/CompareKnowledgeProgress/CompareKnowledgeProgressQueryHandler.cs (~13) -- same
  • SpikerSoft.Business/Domain/Activity/Queries/GetActivityCoverage/GetActivityCoverageQueryHandler.cs (~13-16) -- UserActivities.ToListAsync() then in-memory filter

Problem: Currently fine for demo data, but at any meaningful user count this becomes O(N) per request and a memory pressure source.

Fix: Push filters into the LINQ query, use Where(...).FirstOrDefaultAsync() or Where(...).ToListAsync() after filtering, not before.

Acceptance criteria:

  • All three handlers query only the relevant rows
  • EF Core query plans verified (logged SQL)
  • No behavior regression in tests
**Severity:** High (perf/scalability) **Files:** - `SpikerSoft.Business/Domain/Activity/Queries/GetChildKnowledgeProgress/GetChildKnowledgeProgressQueryHandler.cs` (~16) -- `UserProfiles.ToListAsync()` to find one user - `SpikerSoft.Business/Domain/Activity/Queries/CompareKnowledgeProgress/CompareKnowledgeProgressQueryHandler.cs` (~13) -- same - `SpikerSoft.Business/Domain/Activity/Queries/GetActivityCoverage/GetActivityCoverageQueryHandler.cs` (~13-16) -- `UserActivities.ToListAsync()` then in-memory filter **Problem:** Currently fine for demo data, but at any meaningful user count this becomes O(N) per request and a memory pressure source. **Fix:** Push filters into the LINQ query, use `Where(...).FirstOrDefaultAsync()` or `Where(...).ToListAsync()` after filtering, not before. **Acceptance criteria:** - [ ] All three handlers query only the relevant rows - [ ] EF Core query plans verified (logged SQL) - [ ] No behavior regression in tests
spikerj commented 2026-05-05 04:46:25 +00:00
Author
Owner
Copy Link
Copy Source

Resolved.

All three handlers now push filters into the LINQ query rather than loading full tables:

  • GetChildKnowledgeProgressQueryHandler -- replaced UserProfiles.ToListAsync() + in-memory FirstOrDefault with a single FirstOrDefaultAsync(p => p.KeycloakUserId == request.ChildUserId)
  • CompareKnowledgeProgressQueryHandler -- replaced full-table scan with Where(p => request.UserIds.Contains(p.KeycloakUserId)).ToListAsync(), then dictionary lookup for both auth gate and display-name lookup
  • GetActivityCoverageQueryHandler -- pushes the optional userId filter into the IQueryable before materialising

Files changed:

  • SpikerSoft.Business/Domain/Activity/Queries/GetChildKnowledgeProgress/GetChildKnowledgeProgressQueryHandler.cs
  • SpikerSoft.Business/Domain/Activity/Queries/CompareKnowledgeProgress/CompareKnowledgeProgressQueryHandler.cs
  • SpikerSoft.Business/Domain/Activity/Queries/GetActivityCoverage/GetActivityCoverageQueryHandler.cs
**Resolved.** All three handlers now push filters into the LINQ query rather than loading full tables: - `GetChildKnowledgeProgressQueryHandler` -- replaced `UserProfiles.ToListAsync()` + in-memory `FirstOrDefault` with a single `FirstOrDefaultAsync(p => p.KeycloakUserId == request.ChildUserId)` - `CompareKnowledgeProgressQueryHandler` -- replaced full-table scan with `Where(p => request.UserIds.Contains(p.KeycloakUserId)).ToListAsync()`, then dictionary lookup for both auth gate and display-name lookup - `GetActivityCoverageQueryHandler` -- pushes the optional `userId` filter into the `IQueryable` before materialising Files changed: - `SpikerSoft.Business/Domain/Activity/Queries/GetChildKnowledgeProgress/GetChildKnowledgeProgressQueryHandler.cs` - `SpikerSoft.Business/Domain/Activity/Queries/CompareKnowledgeProgress/CompareKnowledgeProgressQueryHandler.cs` - `SpikerSoft.Business/Domain/Activity/Queries/GetActivityCoverage/GetActivityCoverageQueryHandler.cs`
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
agentic
Actionable surfaced by the agentic architecture & security review
 bug
Something is not working
 duplicate
This issue or pull request already exists
 enhancement
New feature
 help wanted
Need some help
 invalid
Something is wrong
 question
More information is needed
 sonarqube
Code quality issue surfaced by SonarQube (bug / code smell)
 wontfix
This won't be fixed
No labels
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
spikerj (Joseph Spiker)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: spikerj/spikersoft-issues#60