SQL 300ms, API 12s: The Hidden Cost of Returning 100k Records to Frontend

Problem: Fast SQL but Slow API

A backend "query all customers" endpoint took ~12 seconds to respond. The SQL alone executed in 327 ms. Adding indexes or rewriting the query did not help. Instrumenting each phase revealed the breakdown:

SQL Query:          327ms
Entity -> DTO:     1820ms
Controller Return: 9264ms
Total:            11431ms

The controller code looked ordinary:

@GetMapping("/customers")
public List<CustomerResponse> list() {
    List<CustomerEntity> customers = customerRepository.findAll();
    return customers.stream()
        .map(this::convert)
        .toList();
}

The real bottleneck occurred after the controller returned — during JSON serialization and HTTP response writing.

Root Cause: Return ≠ Response Sent

In a @RestController, the returned object must pass through Spring MVC's HttpMessageConverter (Jackson) which traverses the entire list, serializes each object to JSON, and writes bytes to the HTTP response. The 300 ms SQL time was only the first step in a long chain.

Multiple In-Memory Copies

Each record passed through several representations:

Database result set

100,000 CustomerEntity objects (JPA)

100,000 CustomerResponse DTOs (to avoid exposing entities)

JSON serialization (Jackson)

HTTP response bytes

Browser parsing 100,000 JSON objects

If intermediate code created a Map or used Collectors.groupingBy, even more container objects were allocated.

GC Pressure and Heap Spikes

Heap usage jumped from 1.2 GB to 2.6 GB per request. With 5–6 concurrent requests, the allocation rate soared, triggering frequent Young GC, rapid Old Gen growth, longer GC pauses, and potential Full GC. This is why repository.findAll() in an HTTP endpoint is dangerous — the developer must ask: "What is the maximum row count for this table?"

DTO Projection: Partial Improvement

Switching to a projection interface that selects only needed columns (id, customerNo, name, mobile, status, createdAt) avoids loading full entities and reduces per-object size. However, if the endpoint still returns 100,000 records, the fundamental problems remain: 100k objects created, 100k JSON strings generated, tens of MB transmitted, browser parsing and rendering. Projection is an optimization, not a license for unlimited data.

Product Clarification: Search vs. Export

Product said users "want to see all" and might "search or export". Search should be done in the database with WHERE clauses and pagination. Export should be an asynchronous task, not a massive JSON payload sent to the browser. The single endpoint was split into: GET /customers?page=0&size=50 for paginated UI queries POST /customer-export-tasks for background Excel/CSV generation

This reduced typical request payload from 100,000 rows to 50 rows, eliminating most JVM tuning needs.

Streaming Response: Not a Silver Bullet

Using StreamingResponseBody with a JsonGenerator can stream records directly to the output stream, lowering server peak memory. However, it does not solve the client-side costs: tens of MB over the network, JSON.parse of 100k objects, browser memory bloat, and potential 100k DOM rows. Streaming also complicates error handling — if the database fails after 30,000 records have been sent, the response header and partial JSON are already delivered, making it impossible to return a clean error JSON. Streaming suits file downloads and explicit exports, not regular list pages.

Pagination Caveats

PageRequest.of(page, 50)

with Page triggers a SELECT COUNT(*) which can become slow on large datasets. If the UI only needs "load more" without a total count, Slice or keyset pagination avoids the extra query.

JSON Field Count Matters

The original CustomerResponse had 35 fields; the UI displayed only 8. Extra null fields added hundreds of bytes per record — tens of MB for 100k rows. The fix: a lean CustomerListItem record with 6 fields for lists, and a separate CustomerDetailResponse for detail views.

Hidden Cost: Getters May Trigger Lazy Loading

In JPA, a getter like entity.getDepartment().getName() can fire a lazy-loading query. If 100k entities each trigger an extra query during DTO mapping, the database cost moves from the repository call to the mapping phase. Logging only repository.findAll() time (300 ms) misleads investigators. Full pipeline timing should measure: DB Query, DTO Mapping, JSON Serialization, Response Write.

Controller Timing ≠ Real Latency

A log statement placed before return result; captures only controller execution (e.g., 2 s), while APM/Nginx shows 11 s and the user waits 12 s. The missing time is JSON serialization and response writing. Spring MVC continues processing after the controller returns. Monitoring Response Size is a cheap, high-value metric: a sudden jump from 32 KB average to 12 MB average signals pagination loss, filter failure, DTO bloat, or data growth beyond original design.

Async Export Pattern

True bulk export should be a separate capability:

Client calls POST /api/customer-export-tasks, receives { "taskId": "EXP20261113001", "status": "PENDING" } Background worker reads in batches (1,000–2,000 rows), writes Excel/CSV incrementally

On completion, uploads to object storage and provides a download URL

This decouples processing from HTTP timeouts, servlet threads, huge JSON, and browser memory. "Query is for humans to view; export is for machines to process."

Conclusion: Simplicity Over JVM Tuning

No advanced GC flags, heap sizing, or Jackson buffer tweaks were needed. The fixes were straightforward:

100k rows → paginated 50 rows

30+ field entities → projection with only needed columns

One giant DTO → separate ListItem and DetailResponse JSON export → async export task

Afterward, the 12-second endpoint returned to normal latency and heap spikes disappeared. The lesson: the most effective performance optimization is not making Java process massive data faster, but confirming that the data needs to enter Java at all. New diagnostic habit: ask not just "How long did SQL take?" but "How many rows returned? How many objects created? Was DTO mapping a copy? How much JSON serialized? How many MB in the HTTP response?" The database may be the fastest link in the chain.