LCFS - Optimize ComplianceReport query eager loading and joins
- Dominant language
- Python
- Stars
- 6
- Forks
- 6
- Avg merge
- 2d 4h
- Merged PRs (30d)
- 87
Description
**Describe the task**
Optimize the ComplianceReport repository queries which use deep nested `joinedload()` chains (lines 77-113 in `compliance_report/repo.py`) that can produce cartesian products and excessive data transfer.
**Purpose**
ComplianceReport queries are the most complex in the system with multi-level eager loading (organization → address, history → status → user → organization). These queries are hit on every report view and list page, making them a top performance bottleneck.
**Acceptance Criteria**
- [ ] Profile current query execution time and generated SQL
- [ ] Replace deep `joinedload()` chains with targeted `selectinload()` or `subqueryload()` where appropriate
- [ ] Eliminate cartesian product scenarios
- [ ] Add database indexes on frequently queried FK columns (organization_id, compliance_period_id, current_status_id)
- [ ] Verify no N+1 regressions introduced
- [ ] Measure and document query time improvement
**Additional context**
- File: `backend/lcfs/web/api/compliance_report/repo.py` (lines 77-113, 206-264)
- `get_assessed_compliance_report_by_period()` has 4+ join operations with outer joins
- Related: ComplianceReport model at `backend/lcfs/db/models/compliance/ComplianceReport.py` has `assigned_analyst` with `lazy="selectin"`
Contributor guide
Assessment
This issue has not been assessed yet.