Query Performance Patterns

<context>
I'm auditing a Next.js application for N+1 query problems that cause performance bottlenecks.
 
My application setup:
- Next.js with App Router
- ORM: Prisma / Drizzle / TypeORM / raw SQL queries
- Database: PostgreSQL / MySQL / SQLite
- Server Components in /app/**/*.tsx
- Data fetching utilities in /lib/db/**/*.ts
</context>
 
<current-implementation>
I have multiple pages and API routes that fetch data from the database. Some may be running queries sequentially when they could be parallel or optimized with joins. I need to identify these bottlenecks systematically.
</current-implementation>
 
<problems>
1. **Hard to spot:** Sequential queries look normal in code but create massive performance waterfalls
2. **Hidden in loops:** Queries inside .map() or for loops are especially problematic
3. **Nested relationships:** Loading related data one record at a time instead of eager loading
4. **Mixed patterns:** Some files use parallel queries, others sequential - inconsistent
</problems>
 
<questions>
1. **Detection strategy:** What code patterns indicate N+1 problems?
   - Multiple await statements in sequence without dependencies?
   - await inside .map(), .forEach(), or for loops?
   - Missing ORM include/with for related data?
   - Queries that reference results from previous queries?
 
2. **Analysis approach:** Should I:
   - Parse AST to find sequential await patterns?
   - Use regex to detect suspicious code structures?
   - Instrument code with timing to measure actual impact?
   - Create a static analyzer that flags potential waterfalls?
 
3. **ORM-specific patterns:** How do I detect N+1 in:
   - **Prisma:** Missing include when accessing relations?
   - **Drizzle:** Not using with() for joins?
   - **TypeORM:** Missing relations in find() options?
   - **Raw SQL:** Multiple sequential queries that could be JOINed?
 
4. **False positives:** How do I avoid flagging:
   - Sequential queries that MUST run in order (dependent on previous results)?
   - Write operations that intentionally run sequentially for data consistency?
   - Cached queries that don't actually hit the database?
 
5. **Fix recommendations:** For each detected N+1, should I recommend:
   - Specific code changes (from sequential to Promise.all() to optimized join)?
   - Expected performance improvement (e.g., "1200ms to 150ms")?
   - ORM-specific syntax for eager loading?
</questions>
 
<specific-scenario>
Example N+1 problem in a post detail page:
 
    async function getPostData(slug: string) {
      // Query 1: Get post (400ms)
      const post = await db.post.findUnique({ where: { slug } })
 
      // Query 2: Get author (400ms) - WAITS for Query 1
      const author = await db.user.findUnique({ where: { id: post.authorId } })
 
      // Query 3: Get comments (400ms) - WAITS for Query 2
      const comments = await db.comments.findMany({
        where: { postId: post.id }
      })
 
      return { post, author, comments }
    }
 
Expected analysis:
- N+1 detected: 3 sequential queries (2 could be parallel)
- Current performance: 1200ms (400ms × 3)
- Fix #1: Use Promise.all() for author+comments: 800ms (1.5x faster)
- Fix #2: Use Prisma include results in 150ms (8x faster)
 
Suggested code:
 
    // Option 1: Parallel queries for related data
    const post = await db.post.findUnique({ where: { slug } })
    const [author, comments] = await Promise.all([
      db.user.findUnique({ where: { id: post.authorId } }),
      db.comments.findMany({ where: { postId: post.id } })
    ])
 
    // Option 2: Optimized with includes (best)
    const postData = await db.post.findUnique({
      where: { slug },
      include: { author: true, comments: true }
    })
 
Another example - N+1 in a loop:
 
    // CRITICAL N+1: Query inside loop
    const users = await db.users.findMany()
    for (const user of users) {
      // This runs a query for EACH user - 100 users = 100 queries!
      user.posts = await db.posts.findMany({ where: { authorId: user.id } })
    }
 
Expected analysis:
- CRITICAL: Query inside loop (N+1 problem)
- Current: 100 users × 200ms = 20 seconds!
- Fix: Use eager loading with include
 
Suggested code:
 
    const users = await db.users.findMany({
      include: { posts: true }
    })
    // Single query with JOIN - 300ms total
</specific-scenario>
 
Generate a Node.js analysis script that scans my codebase for N+1 query patterns, produces a detailed report with performance impact estimates, and provides specific fix recommendations with code examples. Prioritize findings by severity (critical for loops, high for sequential, medium for potential optimizations).