Faith & Code | Reflections by Errong

How to Optimize a Slow JPA Query Processing 40K+ IDs (Real Performance Tuning Guide) Handling large datasets efficiently is one of the most common challenges in backend engineering. In this post, I’ll walk through how a query processing 40,000+ IDs went from ~24 seconds down to a few seconds using practical optimization techniques. All domain-specific details have been intentionally removed to focus purely on reusable engineering patterns. 📉 The Problem A service fetches data using a JPA query with a large IN clause and joins between tables: SELECT new SomeDto( A.primaryId, A.field1, A.field2, B.field3, C.metric1, C.metric2 ) FROM EntityA A LEFT JOIN A.relatedEntity B LEFT JOIN EntityC C ON C.refId = A.primaryId WHERE A.primaryId IN :ids Even with chunking (~900 IDs per query), total execution time was around 24 seconds . 🚨 Root Causes Unoptimized JOIN conditions Missing database indexes Sequential query execution ORM (JPA) over...

How We Optimized a Spring Boot Pagination Query by 36× (From 1.8 Minutes to 3 Seconds) In high-traffic backend systems, slow database queries are often the hidden bottleneck that quietly kills performance. In our case, a paginated query powering a reporting API was taking 1.8 minutes to respond under load. After optimization, we reduced it to ~3 seconds . 36× faster ~97.2% less execution time ~3500% performance improvement Here’s how we did it — and the key lessons you can apply immediately. 🧱 The Problem: Slow Pagination Query We had a Spring Data JPA query backing a paginated API. It involved multiple filters, joins, and was returning a large dataset. Original issues: Full entity loading Heavy object graph hydration Unnecessary columns fetched Inefficient filtering logic Expensive pagination count The database schema (simplified and anonymized) looked like this: TABLE txn_line ( line_id BIGINT, order_id BIGINT, product_code ...

How a JPA save() Call Silently Overwrote My Data (and How to Fix It) I recently encountered a subtle but dangerous issue in a Spring Boot + JPA application. There were no errors, no exceptions, and no failed transactions — yet a database column was silently overwritten with NULL . If you are using Spring Data JPA and updating the same table from multiple endpoints, this is something you should be aware of. The Simplified Scenario We had two REST endpoints updating the same entity, but different fields. The code below is simplified and mangled for illustration. Endpoint A – updates an external reference EntityX entity = repository.findByKey(refId); // update external reference entity.setExternalRef("ABC123"); repository.save(entity); Endpoint B – updates customer email EntityX entity = repository.findByKey(refId); // update email only entity.setEmail("user@example.com"); repository.save(entity); The assumption was that Endpoint B would ...

named parameter bind only works on the where clause. what if you want different schema at runtime for the table part. such as 'select * from :tablename where ...'   'update :tablename set ..." You will find the above won't work in your @Query annotation use EntityManager::createNativeQuery You can get the single result, the first result, result lists or result stream. use entityManager.createNativeQuery(sql).executeUpdate() use executeUpdate to update or delete records in the table Not allowed to create transaction on shared EntityManager Caused by: java.lang.IllegalStateException: Not allowed to create transaction on shared EntityManager - use Spring transactions or EJB CMT instead Please call joinTransaction before execute update sql