# Tag Photos Performance Analysis

## Executive Summary

The Tag Photos page has significant performance bottlenecks, primarily in the backend database queries. The current implementation uses an N+1 query pattern that results in thousands of database queries for large photo collections.

## Current Performance Issues

### 1. Backend: N+1 Query Problem (CRITICAL)

**Location:** `src/web/services/tag_service.py::get_photos_with_tags()`

**Problem:**
- Loads all photos in one query (line 238-242)
- Then makes **4 separate queries per photo** in a loop:
  1. Face count query (line 247-251)
  2. Unidentified face count query (line 254-259)
  3. Tags query (line 262-269)
  4. People names query (line 272-280)

**Impact:**
- For 1,000 photos: **1 + (1,000 × 4) = 4,001 database queries**
- For 10,000 photos: **1 + (10,000 × 4) = 40,001 database queries**
- Each query has network latency and database processing time
- This is the primary cause of slow loading

**Example Timeline (estimated for 1,000 photos):**
- Initial photo query: ~50ms
- 1,000 face count queries: ~2,000ms (2ms each)
- 1,000 unidentified face count queries: ~2,000ms
- 1,000 tags queries: ~3,000ms (3ms each, includes joins)
- 1,000 people names queries: ~3,000ms (3ms each, includes joins)
- **Total: ~10+ seconds** (depending on database performance)

### 2. Backend: Missing Database Indexes

**Potential Issues:**
- `Face.photo_id` may not be indexed (affects face count queries)
- `PhotoTagLinkage.photo_id` may not be indexed (affects tag queries)
- `Face.person_id` may not be indexed (affects people names queries)
- Composite indexes may be missing for common query patterns

### 3. Frontend: Loading All Data at Once

**Location:** `frontend/src/pages/Tags.tsx::loadData()`

**Problem:**
- Loads ALL photos and tags in a single request (line 103-106)
- No pagination or lazy loading
- For large collections, this means:
  - Large JSON payload (network transfer time)
  - Large JavaScript object in memory
  - Slow initial render

**Impact:**
- Network transfer time for large datasets
- Browser memory usage
- Initial render blocking

### 4. Frontend: Expensive Computations on Every Render

**Location:** `frontend/src/pages/Tags.tsx::folderGroups` (line 134-256)

**Problem:**
- Complex `useMemo` that:
  - Filters photos
  - Groups by folder
  - Sorts folders
  - Sorts photos within folders
- Runs on every state change (photos, sortColumn, sortDir, showOnlyUnidentified)
- For large datasets, this can take 100-500ms

**Impact:**
- UI freezes during computation
- Poor user experience when changing filters/sorting

### 5. Frontend: Dialog Loading Performance

**Location:** `frontend/src/pages/Tags.tsx::TagSelectedPhotosDialog` (line 1781-1799)

**Problem:**
- When opening "Tag Selected Photos" dialog, loads tags for ALL selected photos sequentially
- Uses a `for` loop with await (line 1784-1792)
- No batching or parallelization

**Impact:**
- If 100 photos selected: 100 sequential API calls
- Each call takes ~50-100ms
- **Total: 5-10 seconds** just to open the dialog

### 6. Frontend: Unnecessary Re-renders

**Problem:**
- Multiple `useEffect` hooks that trigger re-renders
- Folder state changes trigger full re-computation
- Dialog open/close triggers full data reload (line 1017-1020, 1038-1041, 1108-1112)

**Impact:**
- Unnecessary API calls
- Unnecessary computations
- Poor perceived performance

## Optimization Recommendations

### Priority 1: Fix Backend N+1 Query Problem (HIGHEST IMPACT)

**Solution: Use JOINs and Aggregations**

Replace the loop-based approach with a single optimized query using:
- LEFT JOINs for related data
- GROUP BY with aggregations
- Subqueries or window functions for counts

**Expected Improvement:**
- From 4,001 queries → **1-3 queries**
- From 10+ seconds → **< 1 second** (for 1,000 photos)

**Implementation Approach:**
```python
# Use SQLAlchemy to build a single query with:
# - LEFT JOIN for faces (with COUNT aggregation)
# - LEFT JOIN for tags (with GROUP_CONCAT equivalent)
# - LEFT JOIN for people (with GROUP_CONCAT equivalent)
# - Subquery for unidentified face count
```

### Priority 2: Add Database Indexes

**Required Indexes:**
- `faces.photo_id` (if not exists)
- `faces.person_id` (if not exists)
- `phototaglinkage.photo_id` (if not exists)
- `phototaglinkage.tag_id` (if not exists)
- Composite index: `(photo_id, person_id)` on faces table

**Expected Improvement:**
- 20-50% faster query execution
- Better scalability

### Priority 3: Implement Pagination

**Backend:**
- Add `page` and `page_size` parameters to `get_photos_with_tags_endpoint`
- Return paginated results

**Frontend:**
- Load photos in pages (e.g., 100 at a time)
- Implement infinite scroll or "Load More" button
- Only render visible photos (virtual scrolling)

**Expected Improvement:**
- Initial load: **< 1 second** (first page only)
- Better perceived performance
- Lower memory usage

### Priority 4: Optimize Frontend Computations

**Solutions:**
1. **Memoization:** Better use of `useMemo` and `useCallback`
2. **Virtual Scrolling:** Only render visible rows (react-window or similar)
3. **Debouncing:** Debounce filter/sort changes
4. **Lazy Loading:** Load folder contents on expand

**Expected Improvement:**
- Smooth UI interactions
- No freezing during filter/sort changes

### Priority 5: Batch API Calls in Dialogs

**Solution:**
- Create a batch endpoint: `GET /api/v1/tags/photos/batch?photo_ids=1,2,3...`
- Load tags for multiple photos in one request
- Or use Promise.all() for parallel requests (with limit)

**Expected Improvement:**
- Dialog open time: From 5-10 seconds → **< 1 second**

### Priority 6: Cache and State Management

**Solutions:**
1. **SessionStorage:** Cache loaded photos (already partially done)
2. **Optimistic Updates:** Update UI immediately, sync in background
3. **Incremental Loading:** Load only changed data after mutations

**Expected Improvement:**
- Faster subsequent loads
- Better user experience

## Performance Metrics (Current vs. Optimized)

### Current Performance (1,000 photos)
- **Initial Load:** 10-15 seconds
- **Filter/Sort Change:** 500ms-1s (UI freeze)
- **Dialog Open (100 photos):** 5-10 seconds
- **Database Queries:** 4,001 queries
- **Memory Usage:** High (all photos in memory)

### Optimized Performance (1,000 photos)
- **Initial Load:** < 1 second (first page)
- **Filter/Sort Change:** < 100ms (smooth)
- **Dialog Open (100 photos):** < 1 second
- **Database Queries:** 1-3 queries
- **Memory Usage:** Low (only visible photos)

## Implementation Priority

1. **Phase 1 (Critical):** ✅ Fix backend N+1 queries - **COMPLETED**
   - Rewrote `get_photos_with_tags()` to use 3 queries instead of 4N+1
   - Query 1: Photos with face counts (LEFT JOIN + GROUP BY)
   - Query 2: All tags for all photos (single query with IN clause)
   - Query 3: All people for all photos (single query with IN clause)
   - Expected improvement: 10+ seconds → < 1 second for 1,000 photos

2. **Phase 2 (High):** Add database indexes
3. **Phase 3 (High):** Implement pagination
4. **Phase 4 (Medium):** Optimize frontend computations
5. **Phase 5 (Medium):** Batch API calls in dialogs
6. **Phase 6 (Low):** Advanced caching and state management

## Testing Recommendations

1. **Load Testing:** Test with 1,000, 5,000, and 10,000 photos
2. **Database Profiling:** Use query profiling to identify slow queries
3. **Frontend Profiling:** Use React DevTools Profiler
4. **Network Analysis:** Monitor API response times
5. **User Testing:** Measure perceived performance

## Additional Considerations

1. **Progressive Loading:** Show skeleton screens while loading
2. **Error Handling:** Graceful degradation if queries fail
3. **Monitoring:** Add performance metrics/logging
4. **Documentation:** Document query patterns and indexes

