Your API Returns 10,000 Records and the Page Takes 12 Seconds. How Do You Fix It?

Backend Engineering InsightsOct 30, 2026

Backend Engineering Insights

Real-world backend engineering explained simply.

Your API Returns 10,000 Records and the Page Takes 12 Seconds. How Do You Fix It?

Java & Spring Boot Interview Questions

Raja Anand

Oct 30

This is one of those interview questions that sounds simple and quietly separates people who have run real systems from people who have only built tutorials.

“Your API returns 10,000 records and the page takes 12 seconds to load. How do you fix it?”

Most candidates say “add pagination” and stop. That is a correct answer, but it is a one-line answer to a question that deserves a structured investigation. In this post I’ll walk through how I would actually approach it, in the order I would do it in production.

Step 0: Don’t Guess. Measure Where the 12 Seconds Go

Before you change any code, find out where the time is spent. Twelve seconds is rarely one problem. It is usually several small ones stacked together:

Database time: how long the query takes to run
Application time: mapping entities to DTOs, lazy loading, serialization
Network time: the size of the JSON payload travelling to the client
Client time: the browser trying to render 10,000 rows

A quick way to split these up is to hit the API with curl and look at the timing, then compare it with the database query time in your logs. If the query takes 300 ms but the response takes 12 seconds, the problem is not the database. If you are on Spring Boot, turn on Actuator metrics and add spring.jpa.show-sql=true (or better, a datasource proxy) in a test environment so you can see exactly what Hibernate is firing.

The interviewer wants to hear this first. Measuring before fixing is the mark of a senior engineer.

Step 1: Fix the Real Problem, You Are Returning Too Much

The most important question is: does the user actually need 10,000 records at once?

Almost never. Nobody reads 10,000 rows. They look at the first 20 or 50, then search or filter. So the first fix is to stop sending everything.

Pagination

With Spring Data JPA this is built in:

@GetMapping("/orders")
public Page<OrderDto> getOrders(Pageable pageable) {
    return orderService.getOrders(pageable);
}

public interface OrderRepository extends JpaRepository<Order, Long> {
    Page<Order> findByStatus(String status, Pageable pageable);
}

The client calls /orders?page=0&size=50&sort=createdAt,desc, and you only fetch 50 rows.

Always cap the page size. If you let the client send size=100000, you have the same problem again. Set spring.data.web.pageable.max-page-size=100 or validate it yourself.

Offset vs Keyset Pagination (the part that impresses interviewers)

Standard pagination uses OFFSET, and it gets slower on deep pages. OFFSET 100000 LIMIT 50 forces the database to read and throw away 100,000 rows before returning your 50.

For large or infinite-scroll data sets, use keyset (seek) pagination:

SELECT * FROM orders
WHERE id < :lastSeenId
ORDER BY id DESC
LIMIT 50;

This uses the index directly and stays fast no matter how deep the user scrolls. The trade-off is that you cannot jump to an arbitrary page number, so it fits feeds and timelines better than numbered page links.

Step 2: Return Only the Columns You Need

Even with pagination, many APIs return full entities with every column and every relationship. If the list screen only shows an order ID, customer name and total, there is no reason to load a 40-column row.

Use projections or DTO queries:

public interface OrderSummary {
    Long getId();
    String getCustomerName();
    BigDecimal getTotal();
}

Page<OrderSummary> findByStatus(String status, Pageable pageable);

Or a constructor expression:

@Query("SELECT new com.example.OrderDto(o.id, o.customerName, o.total) " +
       "FROM Order o WHERE o.status = :status")
Page<OrderDto> findSummaries(String status, Pageable pageable);

This reduces database I/O, memory use, and JSON size in one change.

Step 3: Kill the N+1 Query Problem

This is the silent killer behind a lot of “why is my API slow” tickets.

Imagine each Order has a lazy customer and a list of items. You load 10,000 orders with one query, then your mapper touches order.getCustomer() and order.getItems(). Hibernate fires one extra query per row. That is 1 + 10,000 + 10,000 queries.

How to spot it: look at your SQL logs. If you see the same query repeated hundreds of times with different IDs, that’s N+1.

How to fix it:

@Query("SELECT o FROM Order o JOIN FETCH o.customer WHERE o.status = :status")
List<Order> findWithCustomer(String status);

Or use @EntityGraph:

@EntityGraph(attributePaths = {"customer"})
Page<Order> findByStatus(String status, Pageable pageable);

A caution: don’t JOIN FETCH a collection and paginate in the same query. Hibernate will apply pagination in memory and warn you about it (HHH000104). For collections, fetch the page of IDs first, then load the details in a second query, or use @BatchSize.

Step 4: Add the Right Indexes

If the query itself is slow, run EXPLAIN on it.

EXPLAIN SELECT * FROM orders WHERE status = 'PAID' ORDER BY created_at DESC LIMIT 50;

If you see a full table scan, add an index that matches both the filter and the sort:

CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

A composite index that covers the WHERE and ORDER BY lets the database walk the index and stop after 50 rows instead of sorting the whole table.

Step 5: Reduce the Cost of Serialization and Payload Size

Sometimes the query is fast and the response is still slow, because turning 10,000 objects into JSON takes time and the payload is huge.

Enable response compression. In Spring Boot: server.compression.enabled=true. JSON compresses very well, often by 80-90%.
Don’t return null fields. spring.jackson.default-property-inclusion=non_null trims the payload.
Use short, flat DTOs instead of deeply nested object graphs.
Avoid exposing entities directly. Besides the security risk, it can trigger lazy loading during serialization.

Step 6: Cache What Doesn’t Change Often

If the data is read far more often than it is written (product catalogs, reference data, dashboards), caching saves you from hitting the database at all.

@Cacheable(value = "productList", key = "#page + '-' + #size")
public Page<ProductDto> getProducts(int page, int size) { ... }

Use Redis or Caffeine, set a sensible TTL, and have a clear invalidation strategy. Caching the wrong thing or never invalidating it creates bugs that are harder to debug than the original slowness, so use it deliberately.

Step 7: When You Genuinely Need Large Data

Sometimes the business really does need a big result: an export, a report, a data sync. Then the answer changes.

Stream it. Use StreamingResponseBody or a streamed JPA query so you don’t load everything into memory at once.
Make it asynchronous. Accept the request, generate the file in the background, and notify the user or give them a download link when it’s ready. Don’t hold an HTTP request open for 12 seconds.
Export as CSV, not JSON. It is far smaller and faster to produce for tabular data

Step 8: Don’t Forget the Frontend

Even a perfect API can feel slow if the UI renders 10,000 DOM nodes at once.

Use infinite scroll or virtualized lists, which render only the rows visible on screen.
Add server-side search and filtering, so users narrow the data instead of scrolling it.
Show a loading skeleton so the page feels responsive while data arrives.

The Answer That Gets You the Job

If you get this question in an interview, here is the structure I’d use:

“First I’d measure where the time goes” (database, application, network, or client).
“I’d question whether we need 10,000 records at all” and introduce pagination with a capped page size, mentioning keyset pagination for deep scrolling.
“I’d return only the fields the screen needs” using projections or DTOs.
“I’d check for N+1 queries and missing indexes” using SQL logs and EXPLAIN.
“I’d reduce payload and serialization cost” with compression and lean DTOs.
“I’d cache stable data” where it makes sense.
“For true bulk needs, I’d stream or process asynchronously.”

Notice that this is not a list of random tricks. It is an order of investigation. That is what interviewers are really testing: whether you can diagnose a problem methodically instead of reaching for the first fix you remember.

Quick Checklist

Measured DB time vs app time vs network time
Pagination with a maximum page size
Projections or DTOs instead of full entities
No N+1 queries (checked SQL logs)
Indexes match filter and sort
Compression enabled
Caching for read-heavy, rarely-changing data
Async or streaming for exports
Frontend renders only what’s visible

Your turn: What’s the slowest endpoint you’ve ever had to fix, and what turned out to be the cause? Reply to this email and tell me. I read every response, and the best stories might become a future post.

If this was useful, share it with a developer who is preparing for interviews, and subscribe so you don’t miss the next scenario.

Backend Engineering Insights

By Raja Anand Java | Spring Boot | Microservices | Kafka | System Design

Enjoyed this issue?

Subscribe • Share • Follow