Org Web Adapter

Editing hungryroot/jira/be_6882_analyze_and_optimize_sql_perf_of_calculate_nth_shipped_celery_task.org

:PROPERTIES:
:ID:       174a74fa-78cc-44c6-ad90-d4c1434fe98f
:END:
#+title: BE-6882 Analyze and optimize SQL perf of calculate_nth_shipped celery task

- source :: https://hungryroot.atlassian.net/browse/BE-6882
- tags ::   [[id:3a477c02-e26d-40f7-a719-0aa1cd169dd8][Hungryroot API Project]] 
- epic ::   [[id:86327fb4-0f66-4bc6-9068-c414cea77042][EP-411 Eng Parking Lot]]
- sprint :: [[id:c82bb209-ab05-47b1-8f5d-3eebfb7a808a][HR BE Sprint 103]]
- status :: [[id:87ee4162-0c60-41e4-8863-89e3ca8bf2f5][Status - Code Review]]

* Tasks
** DONE Investigate task and read through description of ticket :project:hungryroot:ep411:be6882:tasks:celery:perf:sprint103:
:PROPERTIES:
:ID:       ec15098d-c9b4-647c-b5ab-0d75a27b69f4
:END:
** DONE Open a pull request with the fix :project:hungryroot:ep411:tasks:be6882:celery:perf:sprint103:
:PROPERTIES:
:ID:       a6e2c1a0-99f3-f6fe-fd11-72c57c656299
:END:
<2026-02-16 Mon>
** DONE Use Jag's suggestion to drop to raw SQL :project:hungryroot:ep411:tasks:be6882:celery:perf:sprint103:
:PROPERTIES:
:ID:       b745c91b-7733-401a-e5ec-f0168e83bb53
:END:
<2026-02-18 Wed>

* 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.