N+1 queries in classification grids and tasks
- Dominant language
- Python
- Stars
- 30
- Forks
- 3
- Avg merge
- 9h 28m
- Merged PRs (30d)
- 42
Description
🤖 Written by Claude.
Classification half of the N+1 sweep in #1723 (the snpdb/annotation/variantopedia half is tracked separately). All of these are fixed by adding `select_related`/`prefetch_related` or by hoisting a per-row query out of a loop.
Structural note: `DatabaseTableView.prepare_results` (`snpdb/views/datatable_view.py:509`) iterates `qs.values(...)`, so `select_related` has no effect there — every N+1 in a DataTable comes from a `renderer=` callback issuing its own query per row. Page size goes up to 100. There is a `pre_render(qs)` hook (line 359) that is the natural place for bulk fetches, currently used only by `ClinVarExportColumns`.
### Grids
**Allele Groupings grid** (`classification/views/allele_grouping_datatables.py:84`) is the worst of these. `render_allele` runs a `ClassificationGrouping` query per row, then `c_hgvs_for` (line 72) walks `latest_classification_modification -> classification -> allele_info -> allele_info[gb]` as lazy FK loads per grouping — roughly five queries per grouping per row. `ClassificationGrouping` already stores `latest_allele_info` (`classification/models/classification_grouping.py:279`), which is the value that chain is reaching for.
The Labs column (line 63) has the same problem: `allele_origin_dict` (`classification_grouping.py:96`) prefetches `classificationgrouping_set` but not `lab`, and `sorted(labs)` then calls `Lab.__lt__`, which touches `organization`.
Other grids doing one fetch per row, both fixable with the `pre_render` hook:
- `classification/views/imported_allele_info_view.py:71` — `Allele.objects.get()` per row.
- `classification/views/clinvar_export_view.py:58` — `allele_for` is a `timed_cache` holding 30 entries against a 100-row page, so it thrashes.
### Tasks
- `classification/models/discordance_models.py:180` — `DiscordanceReport.update()` dereferences `classification_original.classification` per row (two extra queries each), then at line 184 issues one `ClassificationModification.objects.get()` per classification in the clinical context. Runs on every classification publish/withdraw touching a discordant context.
- `classification/models/classification_grouping.py:479` — `update_all_dirty` iterates dirty groupings and `update()` immediately touches `allele_origin_grouping` (line 377). Runs after every classification import, over tens of thousands of rows on a bulk import.
- `classification/views/exports/classification_export_formatter_condition_resolution.py:172` — a `ConditionTextMatch` query per classification plus a `parent` FK walk, at whole-database export scale.
- `classification/models/clinvar_export_prepare.py:178` — two `COUNT` queries per allele in the nightly prepare; collapsible into one `aggregate`.
Contributor guide
No contributing guide indexed for this repository
Research direction
Start with DatabaseTableView.prepare_results and its pre_render hook in snpdb/views/datatable_view.py, then read the named classification grid and task locations to map each per-row query. Done means the listed paths no longer issue repeated queries for each row or classification while preserving the existing grid, export, publish, and import behavior.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python
- Domain
- backend, databases, performance
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Clearly specified
- Newbie friendliness
- 38/100