Affiliate stats sayfasi 8 saniyede aciliyor — N+1 query fix (PR #644 V F4)

Adana Cukurova da 32 yasinda dijital pazarlamaci Pelin (@pelin_yemek 60k TikTok takipci, restaurant SaaS niche), 7 aydir thMenu affiliate program ayda ~80-120 restaurant onboarding. Cuma aksami dashboard 12 saniye yuklendi, her tab switch 8-14 saniye, Conversion Funnel + Per-Restaurant Performance + Top Referring Pages tab lari ayri ayri yavas. Pelin support a yazdi. Support 3 wrong theory busted (bandwidth: 245 Mbps OK, referer logic: direct URL ayni yavas, D1 quota: Pelin ayda 1500 row read quota altinda). 4. theory D1 query latency log: tek dashboard load 200+ ayri D1 query. Pelin 120 conversion = 1 SELECT conversions + 120 SELECT restaurant + 120 SELECT commission = **241 sequential queries**. D1 average latency 30ms → 241 × 30ms = 7.2 saniye sequential + JSON serialization = 8-10s observed. Classic **N+1 anti-pattern** — for each conversion fetch restaurant + for each fetch commission. 1990 lar dan beri programming literature da bilinen, ama low-data thresholds altinda acceptable hizda gorunur. 2024 te shipped olmus thMenu kodu, kucuk affiliate (50 conversion/ay) icin 3 saniye OK, Pelin gibi heavy (120+/ay) icin 8-14 saniye. **PR #644 batch V F4** fix: 241 query → 3 batched query: (1) SELECT conversions (1 query); (2) SELECT restaurants WHERE id IN (...) batched IN-clause + unique ids dedupe; (3) SELECT commissions ROW_NUMBER() OVER PARTITION BY conversion_id ORDER BY created_at DESC ile latest-per-group + filter rn=1. (4) JS-side Map lookup merge — restMap.get + commMap.get O(1). Toplam 3 query × ~30ms = ~90ms + serialization ~200ms. Pelin dashboard load 30s → 600ms. 40-70x speed-up. Conversion Funnel + diger tab lar ayni pattern ile rewrite. Pelin TikTok story sine "thMenu dashboard hizli imis, 40x daha hizli" yazdi 11k view, organic affiliate growth followers larindan. Pattern: N+1 query anti-pattern detection per-request query count metric + threshold alarm (>10 queries) + eager loading shared helper (Promise.all + IN clause + Map merge boilerplate) + code review checklist (for-loop + query pattern flag) + p95 latency dashboard. Low-data thresholds altinda yıllarca silently slow olabilir.

İlgili makaleler