List API Fetches Tens of Thousands of Records at Once, Frontend Needs Pagination and Totals—How to Return Data Without Lag?

When a list API is slow, the first thing to check is often not the database server but the ORM's lazy loading. A typical symptom: a page of 20 records results in more than 20 SELECT statements in the API logs; when the main record count rises to 100, the SQL count also approaches 100. Based on 2026 project delivery practices, it is often more economical to merge related queries first before discussing caching or configuration upgrades. Drawing from hands-on troubleshooting experience, this article explains how to quickly pinpoint the issue, what counts as a sufficient fix, and when N+1 should not be blamed.

A Real Delivery: List Page Improved from 3 Seconds to 0.7 Seconds

In an admin order management page, each page showed 20 records, and paging took 3 seconds. The client initially suspected the database was slow and wanted to scale up. We first enabled the ORM SQL log and found that one request generated 84 SELECT statements: the first query fetched the main order table, and the remaining 83 queries fetched customer, product, and address information one by one based on order IDs. This is a classic lazy-loading N+1 problem.

Constraints: we could not change the table structure, could not introduce new middleware, and the integration period was only one week. Our approach: explicitly eager-load two levels of associations in all list query methods, and also update the export interface because it uses the same query functions. Result: the number of SQL statements per page dropped from 84 to 6, P95 latency dropped from 3 seconds to about 0.7 seconds, and the database connection pool was not expanded. The trade-off was that when adding new filter conditions later, the eager-loading list must be maintained synchronously; otherwise, some logic will fall back to lazy loading.

Based on typical experience range, fixing this kind of lazy-loading N+1 usually reduces P95 latency by 50% to 80%. If there is no noticeable improvement after the fix, the bottleneck is not N+1; you should shift attention to single large queries or missing indexes.

Why Lazy Loading Turns One List Request into Hundreds of Queries

Lazy loading is one of the ORM's default strategies: the program executes a database query only when it actually accesses an associated property. For a detail page that fetches only one record, it saves unnecessary SQL; for a list page, the opposite happens—each row triggers an additional query, so the number of database round-trips increases with the row count.

If an order list needs to display customer, product, and address, default lazy loading will first query the order table, then query associated information row by row. As the order count grows from 20 to 200, database round-trips can grow from dozens to hundreds. Backend systems with large data volumes and many columns are more prone to this issue, but ORM encapsulation often hides the problem.

• Common trigger: accessing an associated property inside a loop that iterates over a collection, e.g., order.customer.name.
• If the debugging panel shows many SQL statements with similar structures that differ only by primary key ID, it is highly likely to be lazy loading.
• To distinguish: if the total number of SQL statements is fixed and does not grow with the row count of the current page, it is not N+1.

Three-Layer Verification: How to Confirm It's N+1, Not Something Else

After receiving a “list is slow” report, it is recommended to confirm the cause based on three layers of evidence. Skipping evidence and directly adding cache or changing database configuration usually only suppresses symptoms temporarily.

1. Check logs: Enable the ORM SQL log, or temporarily enable slow query logs on the database, and capture a single list request. Be careful to filter out other asynchronous polling requests on the page.
2. Count SELECT statements: Count the total number of queries generated by that request, and also record the number of main records on the current page. If the total is above 20 to 30 and grows with the per-page row count, N+1 is likely present.
3. Compare query conditions: Check the subsequent SELECT statements—are their WHERE conditions reusing a primary key ID column from the previous query result? For example, if a where id in (?) becomes row-by-row where id = 1, id = 2..., it is confirmed.

It is advisable to refresh the page three times consecutively before making a judgment, to avoid counting session heartbeats, permission checks, and other queries as part of the list request. If the total number of SQL statements is always single-digit and does not grow with the row count, the slowness is more likely due to a single SQL not using an index, or frontend rendering and network transmission taking up most of the time.

Solution Comparison: Eager Loading, Explicit JOIN, Manual Map

There are three common approaches to fix N+1. Based on project delivery experience, they are sorted by increasing scope of changes; it is advisable to try eager loading first. JOIN is suitable when filter or sort directly depends on associated fields. Manual Map is more for complex queries or cross-service assembly. The following table compares these approaches on a typical admin order list (20 per page, 3 associated tables):

• ORM eager loading: Explicitly specify the associations to be prefetched at the query entry point. Most frameworks have built-in methods; changes are concentrated in query functions and benefits are stable. In the above scenario, SQL can be reduced to 5 to 8 statements, with an average implementation period of about 0.5 to 2 person-days.
• Explicit JOIN: Let the database return the main table plus associated table fields in one go. It suits scenarios where WHERE and ORDER BY use columns from associated tables. When there are more than three levels of associations or many columns in the result set, data transfer and memory usage rise, and the benefit may not be greater than eager loading.
• Manual Map: First query the main table ID set, then query the associated table using WHERE IN to fetch all at once, and finally assemble in memory by ID. It fits cases where ORM models and table structures don't fully match, or data comes from different services. This requires extra assembly code but offers the most flexibility.

Applicability boundaries for the comparison: if the associated table itself has more than a million rows and the WHERE condition can filter down to a very small range, eager loading combined with pagination still risks deep pagination; in such cases, cursor pagination or “query IDs first, then fetch details” is more appropriate. If a list needs to return more than 20 associated fields and most are displayed, explicit JOIN may be faster than multiple IN queries; the decision should be based on load testing with real data, not just theoretical analysis.

What Counts as a Successful Fix: Acceptance and Common Pitfalls

There are three criteria to determine whether the fix is complete: whether the total number of SELECT statements no longer grows with the per-page row count in a fixed request scenario; whether P95 improves as the query count drops; and whether all entry points that share query logic, such as list, export, and batch operations, are updated together. If SQL drops from over a hundred to single digits but the API is still slow, shift focus to single large queries, database connection waits, or redundant frontend rendering.

• Common pitfall: only fixing the list page, not the export or detail pages; later, new endpoints copy old code and the problem recurs.
• Common pitfall: eager-loading too many associations at once, causing memory usage and network transfer to rise; load only two to three levels as needed, and don't SELECT all columns of the entire table.
• Common pitfall: after the fix, only looking at average latency, not P95; averages hide slow requests. It's recommended to monitor P95 and GC time simultaneously.
• Acceptance suggestion: following 2026 habits, run a 10-minute load test in the pre-production environment, observing total SQL count, P95, and application GC time. If SQL stays around 5 to 10 per page and P95 variation is within 20%, it can be considered acceptable.

Applicable Scenarios and Boundaries: Not All Slow Lists Should Be Fixed as N+1

N+1 fixes yield obvious benefits in backend admin panels, report lists, and scenarios with “few rows per page but many associated columns.” If the system has only a few fixed lists and data volume stays below a few thousand rows, not fixing it may not cause obvious failures; once data volume reaches millions, you also need indexes, pagination cursors, or data service, and you cannot rely solely on a single merged query.

• Suitable: short API timeouts, small database connection pools, pages with 10 to 50 rows per page, and many associated dimensions.
• Not suitable: low-frequency internal tool pages, single-item display logic, and scenarios with existing base data caching.
• No need: when slow query logs show a single SQL is the bottleneck, address indexes and filter conditions first, then consider join methods.
• Extra boundary: if list data changes infrequently and the client accepts 30+ second latency, result caching may be cheaper than optimizing SQL; however, most transaction-oriented backends in 2026 no longer accept such latency, so it is still recommended to solve from the query side first.

FAQ

Why does the same API generate hundreds of similar SQL statements?

Mostly because the ORM iterates over main records and issues additional queries to fetch associated tables one by one; after changing associations to explicit eager loading, the SQL count usually drops to single digits.

Can adding an index to the associated table solve N+1?

An index speeds up a single query but does not change the pattern of “looping to access the database”; in N+1 scenarios, merge queries first, then add indexes as needed.

Is JOIN better than eager loading?

Not necessarily. JOIN suits cases where WHERE or ORDER BY requires associated fields; multi-level JOIN bloats the result set and makes pagination harder to maintain.

Should the export API be updated as well?

Yes. Export and list often share the same query logic. If not updated together, you'll see “page fast, export slow,” which can mislead the team during troubleshooting.

Do a SQL sample first before taking any action: count the SELECT statements for the same request and the number of main records. If both grow in tandem, prioritize eager loading or manual query merging. After the change, continue monitoring P95 and also check the export entry point. The above approach mainly applies to ordinary business lists; for extremely large tables or analytical statistics, adjust index strategy and data layering accordingly.