AgentSkillsCN

detect-n-plus-one

当用户关心数据库性能、慢查询、N+1 查询模式，或基于循环的批量获取时，可运用此技能。触发条件包括“N+1”“循环中执行查询”“数据库响应缓慢”“查询次数过多”“批量查询”“性能审计”。

SKILL.md
--- frontmatter
name: detect-n-plus-one
description: Use when user asks about database performance, slow queries, N+1 patterns, or loop-based fetching. Triggers on "N+1", "query in loop", "slow database", "too many queries", "batch queries", "performance audit".

N+1 Query Pattern Detection

Activation Announcement

IMPORTANT: When this skill activates, ALWAYS start your response with:

code
🔍 **N+1 Pattern Detection Scan**
Scanning codebase for loop + query anti-patterns...

This provides a visual indicator to the user that systematic detection is running.

What is N+1?

An N+1 query problem occurs when code:

  1. •Fetches a list of N items
  2. •Then makes 1 additional query per item in a loop

Instead of 1 batch query, you get N+1 queries. For 100 items = 101 DB roundtrips.

Detection Strategy

Phase 1: Semantic Search for Loop + Query Patterns

Run these searches to find suspicious code:

code
fast_search("for loop database query execute", method="semantic", limit=30)
fast_search("foreach await repository find", method="semantic", limit=30)
fast_search("loop api call fetch request", method="semantic", limit=30)
fast_search("iterate collection query each", method="semantic", limit=30)

Phase 2: Pattern Search for Language-Specific Idioms

code
# C# / Entity Framework
fast_search("foreach await context", method="text", limit=20)
fast_search("for var in FirstOrDefault", method="text", limit=20)

# TypeScript / Prisma / TypeORM
fast_search("for of await prisma findUnique", method="text", limit=20)
fast_search("forEach await fetch", method="text", limit=20)

# Python / SQLAlchemy / Django
fast_search("for in session query", method="text", limit=20)
fast_search("for in objects get filter", method="text", limit=20)

# General patterns
fast_search("for each get by id", method="semantic", limit=20)

Phase 3: Trace Suspicious Functions

For each suspicious result, trace the call path:

code
trace_call_path(symbol_name="<function_name>", direction="downstream", max_depth=3)

Look for paths that lead to:

  • •Database access (repository, context, connection, cursor)
  • •HTTP calls (fetch, axios, http client)
  • •Cache lookups without batching

Phase 4: Verify with Code Inspection

code
get_symbols(file_path="<file>", target="<function>", mode="full")

Confirm the pattern by checking:

  • •Is there a loop construct (for, foreach, while, map)?
  • •Is there a query/fetch inside the loop body?
  • •Could this be batched into a single query?

Output Format

IMPORTANT: Always present findings in this structured format:

code
## N+1 Pattern Detection Results

### Summary
- Files scanned: X
- Potential N+1 patterns found: Y
- Confidence: High/Medium/Low

### Findings

#### 1. [HIGH] file/path.ts:42 - `processOrders`
**Pattern**: foreach loop with individual DB fetch
**Code**:
```typescript
for (const order of orders) {
    const customer = await db.customers.findUnique({ where: { id: order.customerId }});
}

Fix: Batch fetch all customers upfront with findMany({ where: { id: { in: customerIds }}})

2. [MEDIUM] file/path.cs:128 - LoadUserDetails

Pattern: LINQ with lazy loading inside loop ...

Recommendations

  1. •Replace individual queries with batch operations
  2. •Use Include/ThenInclude for eager loading (EF Core)
  3. •Consider caching for frequently accessed data
code

## Red Flags to Look For

### High Confidence Indicators
- `await` inside `for`/`foreach`/`for...of` with DB method name
- Loop variable used as query parameter
- `.Find()`, `.Get()`, `.FirstOrDefault()`, `.findUnique()` inside loops
- Multiple queries returning single items in sequence

### Medium Confidence Indicators
- Lazy loading patterns (accessing navigation properties in loops)
- `.Select()` with async operations
- Nested loops with any data access

### Language-Specific Patterns

| Language | High-Risk Patterns |
|----------|-------------------|
| C# | `foreach` + `await context.X.FindAsync()` |
| TypeScript | `for...of` + `await prisma.x.findUnique()` |
| Python | `for x in` + `session.query().filter().first()` |
| Java | `for` + `repository.findById()` |
| Go | `for range` + `db.Query()` or `db.QueryRow()` |

## Success Criteria

This skill succeeds when:
- Clear visual announcement at start
- Systematic search across semantic + pattern modes
- Findings presented with file:line locations
- Confidence levels assigned to each finding
- Actionable fix suggestions provided
- Results are language-appropriate