5 JPQL Patterns That Nearly Killed Our Banking CRM Performance

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 🧵

``