5 JPQL patterns that nearly killed our CRM performance at a banking client. We were shipping customer queries in milliseconds—then scaled to 100k+ records. Here’s what broke us and how we fixed it.
🔴 Pattern #1: The N+1 Query Nightmare
Query: SELECT c FROM Customer c
Looks innocent. Except we fetch INDIVIDUAL, ORGANIZATION, GROUP types—each with separate queries to get their details. 1 query became 100k queries.
✅ Solution: JOIN FETCH
SELECT c FROM Customer c
LEFT JOIN FETCH c.details
WHERE c.status = 'ACTIVE'
One query. All data. Problem solved. We cut response time from 8s to 200ms.
🔴 Pattern #2: Pagination With JOIN FETCH
Query.setFirstResult(0).setMaxResults(100)
Sounds good. Except JPQL pagination happens after the JOIN, so you’re paginating wrong data. Nightmare fuel.
✅ Solution: Two-phase pagination
1. Fetch IDs first: `SELECT c.id FROM Customer c WHERE...`(paginate here)
2. Fetch full objects: `SELECT c FROM Customer c WHERE c.id IN (:ids)`
Double query, but correct results at scale.
🔴 Pattern #3: Fetching Sub-Collections
SELECT c FROM Customer c
LEFT JOIN FETCH c.accounts
LEFT JOIN FETCH c.transactions
You think you’re fetching everything. You’re actually creating a Cartesian product. 1 customer → 1000 rows.
✅ Solution: Separate queries
- Load customers
- Load accounts by customer IDs
- Load transactions by account IDs
More queries, but predictable performance. MapStruct handles the mapping.
The hard lesson: JPQL is magical until it isn’t. Scale reveals everything.
Full deep-dive incoming—patterns, benchmarks, and the exact queries that saved us 6 hours of processing time daily.
Follow for the blog post 🧵
``
