How I Made My Production App 100x Faster: A Tale of N+1 Queries
The Problem
My production app was crawling. The homepage that used to load instantly was now taking 10+ seconds. Users were complaining, and I was getting nervous every time I checked the analytics dashboard.
The database had grown from a few hundred entries to tens of thousands. What worked fine during development was now a disaster in production.
The Investigation
I started by looking at the server logs. Nothing jumped out immediately - no obvious errors, no memory issues, no CPU spikes. Just... slowness.
Then I did what I should have done first: I actually looked at the code that loads the homepage.
Here's what I found (using Drizzle ORM):
async function getTopItemsForCategory(db, category, limit = 5) {
// First, get all item IDs in this category
const taggedItems = await db
.select({ itemId: categoryItems.itemId })
.from(categoryItems)
.where(eq(categoryItems.category, category))
.all()
if (taggedItems.length === 0) return []
// Then, for each item, get its full details
const itemData = await Promise.all(
taggedItems.map(async ({ itemId }) => {
const item = await db
.select()
.from(items)
.where(eq(items.id, itemId))
.get()
return item?.data ? JSON.parse(item.data) : null
})
)
return itemData
.filter(Boolean)
.sort((a, b) => b.popularity - a.popularity)
.slice(0, limit)
}
Looks clean, right? Using a modern ORM, Promise.all for "parallel" execution, proper error handling...
My homepage showed 5 categories, with 5 items each. Seems reasonable, right?
Wrong.
The Trap of Query Builders
Here's the thing about libraries like Drizzle, Prisma, or any query builder: they make database queries look like regular function calls. That's usually great for developer experience, but it masks what's actually happening.
Let me show you what that Drizzle code actually translates to in SQL:
First query:
SELECT item_id FROM category_items WHERE category = ?
-- Returns, say, 100 rows
Then, 100 more queries:
SELECT * FROM items WHERE id = ? -- for item 1
SELECT * FROM items WHERE id = ? -- for item 2
SELECT * FROM items WHERE id = ? -- for item 3
... (97 more times)
The ORM code looks elegant and maintainable. The SQL reality is a performance disaster.
The Hidden Monster: N+1 Queries
Here's what was actually happening:
For a category with 100 items:
- 1 query to get the item IDs
- 100 queries to get each item's details
- Total: 101 queries per category
For my homepage with 5 categories:
- 5 × 101 queries = 505 queries
- On EVERY page load
- With network latency between my app and database
Each query might only take 5-10ms, but when you're making 500+ sequential database calls, those milliseconds add up fast:
- 500 queries × 10ms = 5 seconds minimum
- Add network latency, serialization, parsing...
- Total: 10+ seconds of database time alone
This is the classic N+1 query problem: 1 query to get N items, then N additional queries to get details. It's insidious because it works fine with small datasets but becomes a nightmare as data grows.
The Fix
The solution was surprisingly simple. Instead of making hundreds of tiny queries, make one smart query using a JOIN.
Here's the fixed Drizzle code:
async function getTopItemsForCategory(db, category, limit = 5) {
// OPTIMIZED: Single query with JOIN
const result = await db
.select({
itemId: items.id,
data: items.data,
})
.from(items)
.innerJoin(categoryItems, eq(items.id, categoryItems.itemId))
.where(eq(categoryItems.category, category))
.orderBy(desc(items.popularity))
.limit(limit)
.all()
// Parse the data
return result
.map(row => {
if (row.data) {
try {
return JSON.parse(row.data)
} catch (e) {
return null
}
}
return null
})
.filter(Boolean)
}
Much cleaner, right? And here's what it translates to in SQL:
SELECT items.id, items.data
FROM items
INNER JOIN category_items ON items.id = category_items.item_id
WHERE category_items.category = ?
ORDER BY items.popularity DESC
LIMIT 5
That's it. One query instead of 101.
Notice how both versions use Drizzle's chainable API, but the second one does all the work in SQL rather than in a JavaScript loop. The database is way better at JOINs than your app is at making hundreds of separate queries.
The Results
Before:
- Homepage: 505 queries, ~10-15 seconds load time
- Category pages: ~100 queries, ~3-5 seconds load time
- Search results (cached): 20 queries per cache hit
After:
- Homepage: 5 queries, <500ms load time
- Category pages: 1 query, <100ms load time
- Search results (cached): 1 query per cache hit
The app went from unusable to lightning fast. Users noticed immediately.
Where Else I Found This Pattern
Once I knew what to look for, I found the same problem in three places:
1. The homepage
Loading top items for multiple categories - fixed with JOIN queries
2. Individual category pages
Same pattern, different route - fixed with JOIN queries
3. Cached search results
When reconstructing cached results from stored IDs, I had the same problem with Promise.all:
Before (Drizzle with N+1):
const cachedModels = await Promise.all(
cached_ids.map(async (id) => {
const item = await db
.select()
.from(items)
.where(eq(items.id, id))
.get()
return item?.data ? JSON.parse(item.data) : null
})
)
Which translates to:
SELECT * FROM items WHERE id = ? -- repeated N times
After (Drizzle with IN clause):
const { inArray } = await import('drizzle-orm')
const cachedModels = await db
.select({
id: items.id,
data: items.data,
})
.from(items)
.where(inArray(items.id, cached_ids))
.all()
Which translates to:
SELECT id, data FROM items WHERE id IN (?, ?, ?, ...)
One query instead of N queries.
Lessons Learned
1. Performance issues hide until production
My dev database had 50 entries. The N+1 pattern was invisible. Production had 50,000 entries. Big difference.
2. Always think about query count, not just query complexity
I was focused on optimizing individual queries. I should have been counting how many queries I was making.
3. Query builders can hide performance problems
Libraries like Drizzle, Prisma, and other ORMs make database queries look like regular function calls. This is great for developer experience, but it makes N+1 queries much harder to spot.
When you see Promise.all(items.map(async (item) => db.select()...)), it doesn't look like you're making hundreds of database calls. It looks like clean, modern async code.
But that's exactly what makes it dangerous. The abstraction hides the cost.
Rule of thumb: If you see a loop (or map/forEach) with a database query inside, you probably have an N+1 problem. Even with Promise.all, even with "parallel" execution.
4. Profile with production-like data
Load testing with 50 rows tells you nothing about performance with 50,000 rows.
5. JOINs are your friend
Yes, they can be complex. Yes, they require understanding your schema. But they exist for exactly this reason - to fetch related data efficiently in one round-trip.
The Debugging Process I Wish I'd Followed
Here's what I'll do next time BEFORE code hits production:
- Count queries per request - Add logging to see exactly how many database calls each endpoint makes
- Test with realistic data volumes - Seed the dev database with 10,000+ entries
- Look for loops with queries inside - Any loop that makes a database call is suspect
- Profile the slow paths - Identify which queries are actually slow vs. which ones are just numerous
- Think in terms of round-trips - Every query is a round-trip. Minimize round-trips, not just query time.
The Quick Fix Checklist
If your app is slow and you suspect N+1 queries:
✅ Find the loops: Search your codebase for loops that make database queries
✅ Check for Promise.all/map with queries: Parallel execution doesn't fix N+1, it just makes it less obvious
✅ Use JOINs instead of multiple queries: Fetch related data in one query
✅ Use IN clauses for multiple IDs: Instead of multiple WHERE id = ?, use WHERE id IN (?)
✅ Push filtering to the database: Use WHERE, ORDER BY, and LIMIT in SQL, not in application code
✅ Enable query logging: See exactly what queries are running
Conclusion
A 100x performance improvement sounds dramatic, but it was literally just changing a few functions to make one query instead of hundreds.
The hard part wasn't the fix - it was recognizing the problem. Once I saw the pattern, it was obvious everywhere.
If your app is slow and the database has grown, go count your queries. You might be surprised.
Performance gains:
- Homepage: 505 queries → 5 queries (101x reduction)
- Category pages: ~100 queries → 1 query (100x reduction)
- Cache retrieval: 20 queries → 1 query (20x reduction)
- Load time: 10+ seconds → <500ms (20x faster)
Not bad for a few hours of work.