CCC: Refactor to remove N+1 patterns: batch children & term loads (#3574904) · Issues · project / ai_context

CCC: Refactor to remove N+1 patterns: batch children & term loads

>>> [!note] Migrated issue   Reported by: [kristen pol](https://www.drupal.org/user/8389) Related to !80 >>> [Tracker] Update Summary: [One-line status update for stakeholders] Check-in Date: MM/DD/YYYY Blocked by: [#XXXXXX] (New issues on new lines) Additional Collaborators: @username1, @username2 Metadata is used by the <a href="https://www.drupalstarforge.ai/" title="AI Tracker">AI Tracker.</a> Docs and additional fields <a href="https://www.drupalstarforge.ai/ai-dashboard/docs" title="AI Issue Tracker Documentation">here</a>. [/Tracker] <h3 id="summary-problem-motivation">Problem/Motivation</h3> Follow-up to: <a href="https://www.drupal.org/project/ai_context/issues/3573713" title="Status: Closed (fixed)">#3573713: Full architecture review of CCC in prep for 1.0</a> Replace per-entity child and taxonomy-term loads with batched queries or precomputed maps so selection and rendering flows perform O(1) queries per request rather than O(N). Why this matters Current code issues: <ul> <li>AiContextItem::getChildren() / hasChildren() run an entityQuery per item and call loadMultiple inside loops (src/Entity/AiContextItem.php).</li> <li>AiContextRouter::rankWithKeyword() loads taxonomy terms per item (src/Service/AiContextRouter.php).</li> </ul> These cause N+1 DB queries and high CPU/memory under scale. Fixing this is the highest-impact perf improvement. Acceptance criteria (measurable) For a selection/render of N candidate items: <ul> <li>Child lookup and term label resolution complete using 1–2 batched queries (not N queries).</li> <li>End-to-end selection + render request reduces DB query count by ≥ 80% vs current baseline in a representative test fixture (e.g., N=200).</li> <li>Unit/integration tests assert that getChildren/hasChildren callers use batched paths and tests show query count improvement.</li> <li>No behavioral regressions: child relationships, subcontext inclusion, and term-based ranking results unchanged.</li> </ul> <h3 id="summary-proposed-resolution">Proposed resolution</h3> Files to inspect/change <ul> <li>src/Entity/AiContextItem.php (getChildren, hasChildren, related helpers)</li> <li>src/Service/AiContextRouter.php (rankWithKeyword term loading + ranking loop)</li> <li>src/Service/AiContextRenderer.php (load/translate flows)</li> <li>src/Form/AiContextItemForm.php (countGlobalItems() uses loads)</li> <li>ai_context.module (hooks that load children on delete)</li> <li>tests/ (add/update tests to measure DB queries)</li> </ul> Suggested implementation approach (recommended, non-prescriptive) 1. Add a ChildrenLookup service (e.g., AiContextChildrenService) responsible for: <ul> <li>A single query to get all children IDs for a list of parent IDs and returning a map parent_id => [child_entities].</li> <li>A cached short-lived in-request map for repeated lookups.</li> <li>A batched hasChildren(parent_ids) helper that uses COUNT(...) with GROUP BY or an IN query.</li> </ul> 2. Update consumers: <ul> <li>Selector, renderer, and list builders should call ChildrenLookup once with the batch of parent IDs and use the map rather than per-entity calls.</li> <li>Replace direct calls to AiContextItem::getChildren()/hasChildren() in hot paths with service usage; keep methods on entity as convenience but mark as thin wrappers that delegate.</li> </ul> 3. Taxonomy term batching: <ul> <li>In Router/Renderer, when you have a batch of entities, collect unique term IDs across the batch and call taxonomy_term_storage->loadMultiple(term_ids) once; build a term-label map used in ranking.</li> </ul> 4. Refactor AiContextItem::getTagIds/getTargetMarkup/getDescriptionMarkup to be data-only or thin wrappers to avoid extra loads. 5. Update ai_context.module child-delete hook to use the service for batch clearing instead of loading and saving each child one-by-one; consider direct field updates if safe. 6. Add instrumentation (temporary) to measure query counts and time before/after. Subtasks <ul> <li>[Investigation] Add a small benchmark script or test fixture and record baseline DB queries for representative flows.</li> <li>[Service] Implement AiContextChildrenService with methods loadChildrenForParents(array $parentIds) and hasChildrenForParents(array $parentIds).</li> <li>[Integration] Replace hot-path callers: Selector, Renderer, list builder, form countGlobalItems, hook implementations.</li> <li>[Terms] Batch taxonomy term loads in Router ranking and renderer.</li> <li>[Tests] Add unit tests for the new service and kernel tests measuring query counts/regression for selection/render flows.</li> <li>[Docs] Document the selector→renderer preload contract and deprecate direct per-entity container calls where applicable.</li> <li>[Cleanup] Remove/mark deprecated entity-level static loads or ensure they delegate to the service.</li> </ul> Testing strategy <ul> <li>Add a kernel test that creates a fixture of ~200 context items with mixed parent relationships and tags; execute select+render path and assert DB query count before/after refactor.</li> <li>Unit tests for ChildrenLookup service logic and term batching logic (mock storage).</li> <li>Functional tests to ensure UI features (subcontext, child inclusion, global counts) behave the same.</li> </ul> Risks & mitigations <ul> <li>Risk: behavioral changes if child order or filtering semantics change. Mitigate by keeping same ordering and writing regression tests that compare selected IDs/text before/after.</li> <li>Risk: introducing a service changes DI surfaces. Mitigate by leaving entity convenience methods delegating to the service and adding deprecation notes.</li> <li>Risk: large schema changes unnecessary — avoid schema work; aim for query/logic changes only.</li> </ul> <h3 id="summary-remaining-tasks">Target date or deadline</h3> February 2026 <h3 id="summary-remaining-tasks">Remaining tasks</h3> <ul> <li>Review AI findings</li> <li>Decide if they are correct</li> <li>Decide if the proposed approach is best or propose alternative</li> <li>Update code</li> <li>Review and test</li> </ul> <h3 id="summary-ai-usage">AI usage (if applicable)</h3> [x] AI Assisted Issue This issue was generated with AI assistance, but was reviewed and refined by the creator. [ ] AI Assisted Code This code was mainly generated by a human, with AI autocompleting or parts AI generated, but under full human supervision. [x] AI Generated Code This code was mainly generated by an AI with human guidance, and reviewed, tested, and refined by a human. [ ] Vibe Coded This code was generated by an AI and has only been functionally tested. > Related issue: [Issue #3573713](https://www.drupal.org/node/3573713)

issue