How We Made a Spring Boot Pagination Query 36× Faster (1.8 Minutes → 3 Seconds)

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 VARCHAR,
  device_type VARCHAR,
  device_id VARCHAR,
  phone_number VARCHAR,
  status VARCHAR,
  duration INT,
  flag_a VARCHAR,
  flag_b VARCHAR
);

TABLE txn_contract (
  contract_id BIGINT,
  order_id BIGINT,
  txn_time TIMESTAMP,
  store_code VARCHAR,
  provider VARCHAR,
  customer_first VARCHAR,
  customer_last VARCHAR,
  ref_id VARCHAR,
  account_id VARCHAR
);

The query joined these tables and applied multiple optional filters for reporting.

---

⚠️ The Original Anti-Pattern

The initial approach relied on fetching full entities:

SELECT t
FROM TxnLine t
JOIN t.contract c
WHERE ...

Problems:

• Loads entire entity graph into memory
• Triggers lazy loading (N+1 issue)
• Transfers unnecessary data over the network
• Slows down serialization

For pagination, Hibernate also had to compute total counts inefficiently.

---

✅ The Optimized Approach

1. DTO Projection (Biggest Win)

We switched to a JPQL DTO projection:

SELECT NEW com.example.dto.OrderSummary(
  t.lineId,
  t.orderId,
  t.productCode,
  t.deviceType,
  t.phoneNumber,
  t.status,
  c.txnTime,
  c.storeCode,
  c.provider,
  c.customerFirst,
  c.customerLast
)
FROM TxnLine t
JOIN t.contract c
WHERE ...

✅ Benefits:

• Only required fields are fetched
• No entity hydration
• No lazy loading
• Reduced memory footprint

---

2. Smart Dynamic Filtering

Instead of building multiple queries, we used conditional filters:

(:storeCode IS NULL OR c.storeCode = :storeCode)
AND (:deviceType IS NULL OR t.deviceType = :deviceType)

✅ This allows a single query to serve multiple scenarios efficiently.

---

3. Efficient Pagination with countQuery

For pagination, we explicitly defined a separate count query:

SELECT COUNT(t)
FROM TxnLine t
JOIN t.contract c
WHERE ...

✅ Why this matters:

• Avoids constructing DTOs for counting
• Reduces DB workload
• Improves response time for large datasets

---

4. Index-Friendly Filters

We ensured that high-selectivity fields were indexed:

• txn_time
• provider
• store_code
• product_code

We also avoided patterns like:

• UPPER(column)
• LIKE '%value%'

These prevent index usage and trigger full table scans.

---

📊 The Results

Metric	Before	After
Query Time	~1.8 minutes	~3 seconds
Performance	Baseline	36× faster
Time Reduction	—	~97.2% less

---

🚀 Why This Worked

The performance gain came from a combination of improvements:

• Less data fetched → DTO projection
• Less memory usage → no entities
• Efficient counting → dedicated count query
• Better indexing → optimized WHERE clauses
• No ORM overhead → avoids Hibernate complexity

---

⚠️ Key Lessons

• Never use full entity loading for large paginated queries
• DTO projection should be your default for read-heavy APIs
• Always define a separate countQuery
• Avoid functions in WHERE clauses (kills indexes)
• Measure before and after — optimization without metrics is guessing

---

✅ Final Thoughts

This optimization highlights a common reality in backend engineering:

Most performance problems are not about infrastructure — they are about inefficient data access patterns.

By understanding how JPA, SQL, and pagination work under the hood, you can unlock massive performance gains with relatively small changes.

In our case: 36× faster with just query-level improvements.

---

If you're working on high-scale backend systems (especially with Spring Boot + JPA), this pattern is one of the highest ROI optimizations you can implement.

❤️ Support This Blog

---

If this post helped you, you can support my writing with a small donation. Thank you for reading.

---

Coffee ☕ Thanks 🙏 Extra Support ❤️