bcgov / bcgov/lcfs

LCFS - Optimize FuelSupply and FuelCode repository query chains

Open
#4,101 0 comments 0 reactions 1 assignee Claimed by @AlexZorkin View on GitHub
Fuel Codes Medium python Tech Debt
Dominant language
Python
Stars
6
Forks
6
Avg merge
2d 4h
Merged PRs (30d)
87

Description

**Describe the task**
Optimize the deep join chains in `fuel_supply/repo.py` (lines 43-60) and `fuel_code/repo.py` which load FuelCode, FuelCategory, FuelType with multiple nested relationships (default_carbon_intensities, energy_density, additional_carbon_intensity, energy_effectiveness_ratio).

**Purpose**
Fuel supply queries load deeply nested fuel reference data on every request, including carbon intensity tables and energy data that may not always be needed. The fuel code repository also uses raw SQL `text()` queries that bypass ORM query optimization.

**Acceptance Criteria**
- [ ] Profile current fuel supply list query performance
- [ ] Use lazy loading or deferred loading for nested reference data not needed in list views
- [ ] Optimize fuel code filtering to use ORM queries instead of raw `text()` where possible
- [ ] Verify fuel supply form still loads all required reference data
- [ ] Measure and document improvement

**Additional context**
- `backend/lcfs/web/api/fuel_supply/repo.py` (lines 43-60)
- `backend/lcfs/web/api/fuel_code/repo.py` (lines 116-150) - uses raw SQL text() queries
- FuelCodeListView is a database view that may also need optimization

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.