DEV Community

theDog
theDog

Posted on Edited on

5 JPQL Patterns That Nearly Killed Our Banking CRM Performance

#java #springboot #microservices #jpql

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)`
Enter fullscreen mode Exit fullscreen mode

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 🧵

Gumroad links :
skmjster.gumroad.com/subscribe

Army Soldier Reporting For Iphone.

Track soldiers, assignments, and duty rosters instantly.No backend. No login. Works offline on your phone.Built for military use. Deployed by Greek Army personnel.WHAT YOU GET:- Single HTML file (deployable in seconds)- Works on any phone (iOS/Android)- Stores data locally—no cloud, no servers- Search, filter, export to CSV- Dark mode included- Completely offlineHOW TO USE:1. Download the HTML file2. Email it to yourself3. Open on iPhone via Microsoft Edge4. Pin to home screen (acts like a native app)5. Start trackingPerfect for:- Unit leaders tracking personnel- Duty roster management- Squad assignments- Quick lookups in the fieldNo technical knowledge needed. Works instantly.Includes setup guide + usage instructions.

favicon skmjster.gumroad.com

``

Top comments (0)