Optimize Performance During Development

Show me the span breakdown for /api/recommendations in staging. Which spans take the most time? Is there unnecessary serialization or duplicate DB calls?

Span breakdown for /api/recommendations in staging

Average end-to-end latency: ~2.08 seconds. This is the same across samples — the endpoint is consistently slow, not spiky.

What the trace looks like:
The handler (recommendations.handler) does this, sequentially, for each of 30 recommended products:

1. db.fetch_recommendation_ids — one-time lookup, ~0.76 ms. Returns the list of product IDs for the user.
2. For every product ID, in a loop (30 iterations):
   - db.fetch_product_by_id — SELECT * FROM products WHERE id=?
   - db.fetch_user_preference_match — SELECT preferences FROM users WHERE id=?
   - serialization.deserialize_product_metadata

Top time consumers (averages across all staging requests, last 6h):

Span                                   | Avg duration | Calls per request | Total per request
---------------------------------------|--------------|-------------------|------------------
db.fetch_product_by_id                 | ~44 ms       | 30                | ~1,320 ms (~64%)
db.fetch_user_preference_match         | ~18 ms       | 30                | ~550 ms (~27%)
serialization.deserialize_product_metadata | ~6 ms    | 30                | ~185 ms (~9%)
db.fetch_recommendation_ids            | ~0.9 ms      | 1                 | ~1 ms

The DB calls alone account for roughly 90% of the total request time (~1.87s out of 2.08s), all of it serial.

Yes — there are clear problems:

1. Classic N+1 on products. db.fetch_product_by_id runs 30 times with SELECT * FROM products WHERE id=?.
   The recommendation IDs are already known after the first query. Replace the loop with a single
   SELECT * FROM products WHERE id IN (...) (or a JOIN against the recommendations query).
   Expected savings: ~1.28s → ~50ms.

2. Duplicate DB call — db.fetch_user_preference_match is called 30 times with the same user_id.
   The SQL is SELECT preferences FROM users WHERE id=? and the user doesn't change within a single request.
   This is fetched once per product iteration instead of once per request. Hoist it out of the loop
   (or cache it in a request-scoped variable).
   Expected savings: ~530ms (~25% of total latency gone immediately).

3. Redundant serialization. serialization.deserialize_product_metadata fires 30 times at ~6 ms each.
   This is almost certainly re-deserializing the same blob repeatedly, or deserializing per-row when
   it could be done in bulk. Worth looking at whether it can be lifted out or memoized — ~185 ms to be recovered.

4. SELECT * on products. Pulling every column when the response likely only needs a subset wastes
   bandwidth and deserialization time — contributes to why fetch_product_by_id is ~44 ms per row
   rather than a few ms.

Rough optimization projection:
Batch product fetch + hoist user preference lookup + bulk deserialize → endpoint drops from ~2.08s to
~100-200ms, conservatively a 10x improvement.

The single highest-ROI fix is hoisting fetch_user_preference_match out of the loop — that's a one-line
change for ~25% latency reduction.