- ID
- 174a74fa-78cc-44c6-ad90-d4c1434fe98f
BE-6882 Analyze and optimize SQL perf of calculate_nth_shipped celery task
- source :: https://hungryroot.atlassian.net/browse/BE-6882
- tags :: Hungryroot API Project
- epic :: EP-411 Eng Parking Lot
- sprint :: HR BE Sprint 103
- status :: Status - Code Review
Tasks
DONE Investigate task and read through description of ticket
- ID
- ec15098d-c9b4-647c-b5ab-0d75a27b69f4
DONE Open a pull request with the fix
- ID
- a6e2c1a0-99f3-f6fe-fd11-72c57c656299
DONE Use Jag's suggestion to drop to raw SQL
- ID
- b745c91b-7733-401a-e5ec-f0168e83bb53
Description
The Celery task
app.tasks.order_release.calculate_nth_shipped.calculate_nth_shipped is currently
the longest-running task by SQL query time at p98, according to Datadog metrics.
This suggests that the task is placing significant load on the database and may
be a candidate for query optimization or architectural improvements.
The goal of this ticket is to analyze the SQL behavior of this task, identify
the primary contributors to its database time, and implement optimizations where
appropriate.
Scope / Expectations
Review Datadog traces and metrics for the calculate_nth_shipped Celery task,
with a focus on:
Total SQL time
Query count
Slow or repeated queries
Identify which parts of the task logic are responsible for the majority of
database usage.
Analyze the underlying ORM queries and generated SQL for inefficiencies such as:
Use explain / analyze depesz
N+1 query patterns
Missing or suboptimal indexes
Unnecessary joins or repeated calculations
Large result sets or unbounded queries
Propose and implement optimizations to reduce SQL time, which may include:
Query refactoring or batching
Use of annotations, subqueries, or prefetching
Index additions or adjustments
Validate improvements by comparing pre- and post-change metrics in Datadog.
Success Criteria
Reduction in total SQL query time for the calculate_nth_shipped task, especially
at p98.
Lower query count and/or faster individual query execution.
No regressions in task correctness or order release behavior.
Clear documentation of changes and observed performance improvements.