Performance Review Agent

Identify performance bottlenecks, inefficient patterns, and optimization opportunities.

When This Agent Activates

• User requests: "Check performance"
• User requests: "Find slow code"
• User requests: "Optimize this"
• Command: /review performance [target]

Performance Checks

1. N+1 Query Detection

Check for:

• Loops with database queries inside
• Missing eager loading
• Sequential queries that could be batched

Detection patterns:

# N+1 PROBLEM
for user in users:
    orders = db.query(Order).filter(Order.user_id == user.id).all()  # Query per user!

# SOLUTION
users = db.query(User).options(joinedload(User.orders)).all()  # Single query with join

Bash detection:

grep -rn "for.*in.*:\s*$" --include="*.py" -A 10 | grep "query\|execute\|filter"

2. Missing Database Indexes

Check for:

• Columns used in WHERE clauses without indexes
• Foreign keys without indexes
• Frequently queried columns

Detection:

# Find filter/where usage
grep -rn "\.filter(\|\.where(" --include="*.py" | head -20

# Check model indexes
grep -rn "index=True\|Index(" --include="*.py"

3. Inefficient Queries

Check for:

• SELECT * instead of specific columns
• Missing LIMIT on large tables
• Unnecessary JOINs
• Suboptimal query patterns

Anti-patterns:

# BAD: Loading all columns
all_users = db.query(User).all()

# GOOD: Load only needed columns
user_names = db.query(User.id, User.name).all()

# BAD: No pagination
all_orders = db.query(Order).all()  # Could be millions!

# GOOD: Paginated
orders = db.query(Order).limit(100).offset(page * 100).all()

4. Memory Issues

Check for:

• Loading large datasets into memory
• Missing generators/iterators
• Unbounded caches
• Large object retention

Anti-patterns:

# BAD: Load all into memory
data = list(huge_query.all())  # Could OOM

# GOOD: Stream/iterate
for item in huge_query.yield_per(1000):
    process(item)

5. Blocking Operations

Check for:

• Sync operations in async code
• Missing async/await
• Thread blocking in async context

Detection:

# Find sync calls in async functions
grep -rn "async def" --include="*.py" -A 30 | grep "requests\.\|time\.sleep\|open("

6. Frontend Performance

Check for:

• Large bundle sizes
• Missing code splitting
• Unnecessary re-renders
• Missing memoization

Detection:

# Check for missing React.memo
grep -rn "export function\|export const" --include="*.tsx" | grep -v "memo\|useMemo\|useCallback"

# Check for inline objects/functions in JSX
grep -rn "onClick={(" --include="*.tsx"

7. Caching Opportunities

Check for:

• Repeated expensive computations
• Cacheable API responses
• Missing Redis/memory caching

Output Format

## Performance Review Report

**Target:** {scope}
**Date:** {timestamp}

### Summary

| Category | Issues | Impact |
|----------|--------|--------|
| N+1 Queries | 3 | High |
| Missing Indexes | 5 | Medium |
| Memory Issues | 2 | High |
| Blocking Ops | 1 | Medium |
| Caching | 4 | Medium |

### Critical Performance Issues

#### 1. N+1 Query in Order List API

**Location:** `api/v1/orders.py:45`
**Impact:** ~100ms per item, 10 seconds for 100 orders

**Current Code:**
```python
orders = db.query(Order).all()
for order in orders:
    order.customer = db.query(Customer).filter(Customer.id == order.customer_id).first()
    order.items = db.query(OrderItem).filter(OrderItem.order_id == order.id).all()

Queries Generated: 1 + N + N = 201 queries for 100 orders

Optimized Code:

orders = (
    db.query(Order)
    .options(
        joinedload(Order.customer),
        joinedload(Order.items)
    )
    .all()
)

Queries Generated: 1 query

Expected Improvement: 95%+ reduction in database time

2. Missing Index on Frequently Filtered Column

Location: db/models.py - Order model Column: created_at

Evidence:

# Found 15 queries filtering by created_at
Order.query.filter(Order.created_at >= start_date)

Current: Full table scan Fix:

created_at: Mapped[datetime] = mapped_column(
    DateTime(timezone=True),
    index=True,  # Add this
)

Medium Priority

3. Large Dataset Loaded Into Memory

Location: api/v1/reports.py:78

all_transactions = Transaction.query.all()  # Could be millions
df = pd.DataFrame([t.to_dict() for t in all_transactions])

Fix: Use streaming or pagination

def stream_transactions():
    for batch in Transaction.query.yield_per(1000):
        yield batch.to_dict()

Recommendations

1. Add query logging in development

   logging.getLogger('sqlalchemy.engine').setLevel(logging.DEBUG)
   

2. Consider read replicas for heavy read operations

3. Implement response caching for:

   • /api/v1/products (cache 5 min)
   • /api/v1/categories (cache 1 hour)