Extraneous Fetching: Why Asking for Too Much Data Slows Systems
A performance antipattern where a system reads more rows, columns, or fields than a task needs, and how to fetch only what matters.
In short
Extraneous Fetching is the habit of reading more rows, columns, or fields than the current task needs, which slows a system down.
14 min readFree
What You Will Learn
This lesson teaches a design mistake called Extraneous Fetching. It happens when a system reads more data than a task needs. The extra data might be unused columns, rows the screen never shows, or whole records when only a few fields matter. You will see what the mistake looks like, why it slows software down, how to fix it, and how to spot it. A worked example follows an order history screen in a shopping app.
Think of a shopper who asks a warehouse for everything when they need one box. The warehouse still has to find, pack, and carry all of it. Software behaves the same way. Every extra field has to be found, read, moved across a network, and held in memory.
Key words
- Antipattern
- A common design habit that seems fine at first but causes trouble later, especially under heavy use.
- Fetch
- To ask a storage place, such as a database, for data and bring the answer back to the program.
- Column
- One named field in a table, such as price or name.
- Row
- One record in a table, such as one order.
- Payload
- The data sent in one message, such as the body of a web response.
- I/O (input and output)
- Reading from or writing to a disk, a database, or a network. Each trip takes time.
- API (application programming interface)
- A set of web addresses that a program calls to ask for data or to request an action.
- SELECT *
- A database command that asks for every column of a table.
- Projection
- Choosing only some columns or fields to return.
- Pagination
- Splitting a long list into pages, such as 20 items at a time.
- Covering index
- An index is a sorted lookup list the database keeps to find rows fast. A covering index holds every column a query needs, so the database can answer from the index alone.
What Does Extraneous Fetching Mean?
Extraneous means not needed. Extraneous Fetching is the habit of fetching data that the current job does not use. Microsoft's Azure Architecture Center, a public design guide, groups this habit under antipatterns. The guide notes that antipatterns are common flaws that can break software when it is under stress. In this case, one business task pulls back far more data than it uses, and the extra reading and moving adds delay.
The cost is easy to miss. The page still looks correct during testing, because a test database is small and few people use it. When real traffic and real table sizes arrive, the same extra data starts to slow every request.
What It Looks Like
Extraneous Fetching shows up in a few familiar shapes. Each one looks reasonable when you write it, which is why it spreads.
SELECT * queries
The query asks for every column of a table, even when the screen uses two of them. The habit is quick to type and hard to notice later.
Whole tables, filtered in code
The app loads every row, then keeps only a few in its own memory. The database skipped the filtering work, so the network and memory carried the cost instead.
Full objects for a few fields
The app loads a complete record, such as a product with its description, reviews, and supplier details, and then shows only the name and price.
Oversized API responses
An endpoint sends back every field it knows about, so each response is far larger than any caller needs.
Two kinds of waste are worth telling apart. Column over-fetching means the query returns fields nobody uses. Row over-fetching means it returns more records than the task needs, usually because a filter or a limit is missing or too loose.
Tip
When you review code, count the columns a query returns and compare them with the columns the screen shows. Then count the rows the same way. Any gap is a candidate for removal.
Extra Columns and Extra Rows
Both kinds of waste cost real work. A query that names its columns carefully still over-fetches if it returns millions of rows nobody will read. Column waste can come from a poorly designed SELECT * query, and a large number of rows can be a sign that filtering is missing, such as a missing WHERE clause (a database filter that keeps only matching rows) or a missing LIMIT (a cap on how many rows come back).
Why Does It Hurt?
Extra data is not free. Microsoft's guide says the usual symptoms are high latency (a long wait for each response) and low throughput (less work finished in a given time).
Extra I/O
Every unused column or row must still be read from disk or memory. More reading means more waiting, even on a fast machine.
Bigger payloads
Larger messages take longer to send.
Higher memory use
The app holds everything it fetched until the work is done. Large lists can crowd out other work on the same server.
Load on database and network
Every extra byte crosses the network, and the database spends effort producing it. Under heavy use, that shared work slows every user, not only the one who asked.
Lost covering index chances
Fetching only the required columns can make covering indexes possible and cut I/O.
How Do You Fix It?
The fix has one rule: fetch only what the current job needs, and let the database do the narrowing. Microsoft's guide advises against fetching large volumes of data that may go stale or be thrown away. Work through these steps when you find the pattern.
- 1
Name the columns
List the fields the screen or caller really uses. Replace SELECT * with those column names.
- 2
Filter, sort, and limit in the database
Put WHERE, ORDER BY (sorting), and LIMIT into the query so the database returns only matching rows, in the right order, up to a cap.
- 3
Paginate long lists
Show a page of results, such as 20 items, and fetch the next page only when the user asks for it.
- 4
Use field selection in APIs
Let callers ask for the fields they need, or shape each response into a small data transfer object (DTO), which is a plain object that holds only the fields one screen needs.
- 5
Let the database do the math
Compute totals such as a sum in the database instead of downloading every order and adding them in code. Keep this to work the database handles well, because heavy work inside it can overload the database.
A DTO is a useful shape for the response, and a projection is the query-side version of the same idea. Both say: name the fields first, then fetch them.
Tip
Write the field list before you write the query. If you cannot say why a field is needed, leave it out and add it back only when a real screen asks for it.
Filtering in the Database
Microsoft's example shows a quiet trap. An ORM (object-relational mapper, a library that turns database rows into program objects) normally turns a where clause into database instructions. When the code calls a helper the library cannot translate, such as one that adds days to a date, every row comes back and the program filters them in memory. The fix is to compute the date value first in the program, then pass that finished value into the query so the database does the filtering.
A warning sign is a conversion step that pulls all rows to the program before a condition is checked. When you see one, ask whether the condition can run in the database instead.
What Is Pagination and What Is Its Trap?
Pagination limits each response to one page, which is the right idea for any unbounded list. The way you page still matters. Offset pagination skips a number of rows with a clause such as OFFSET. As the skip grows, the database may still scan all the earlier rows before it reaches the page you want.
A keyset approach avoids that cost. Instead of skipping, it asks for rows after the last one already shown, using an indexed column such as id. Guides on SQL paging recommend this for large tables, and the index keeps the lookup fast.
Worked Example: The Order History Screen
A mobile shopping app has an order history screen. For each order, it shows three things: the order number, the date, and the total. The backend, however, runs SELECT * on the orders table joined with the line items (one row per product in each order), then sends every full order to the phone. The phone then displays three fields and throws the rest away. The steps below show how to fix it.
- 1
Write down what the screen shows
The screen shows the order number, the order date, and the total. Nothing else appears in this list.
- 2
Find the waste in the query
SELECT * asks for every order column. The join also pulls in every line item, which this screen never displays.
- 3
Name the three columns
Ask only for the order number, the date, and the total. Drop the line-item join, because no line item is shown here.
- 4
Sort and cap in the database
Sort by date, newest first, and keep only the latest 20 orders. The database does this work, so the phone receives a short list.
- 5
Page through older orders
When the user scrolls down, request the next page of orders instead of the whole history at once.
- 6
Measure before and after
Compare the query time, the payload size, and the response time on the same test data. The new version should be much smaller and faster.
The step-by-step change is the idea in practice: name the fields, let the database narrow the rows, and page the rest. The sample below uses Python's built-in sqlite3 module to show the before and after queries. It needs no extra installs and runs the same way every time.
Before and After in Code
import sqlite3
db = sqlite3.connect(':memory:')
db.execute('CREATE TABLE orders (id INTEGER PRIMARY KEY, number TEXT, placed_on TEXT, total REAL, gift_note TEXT, billing_address TEXT, shipping_address TEXT, status TEXT)')
for i in range(1, 6):
db.execute('INSERT INTO orders VALUES (?, ?, ?, ?, ?, ?, ?, ?)', (i, 'A-' + str(100 + i), '2026-11-0' + str(i), 10.0 * i, 'gift wrap', '1 Long Street', '9 High Road', 'shipped'))
# Before: every column of every row
everything = db.execute('SELECT * FROM orders ORDER BY id DESC').fetchall()
print('before columns per row:', len(everything[0]))
# After: three columns, newest two orders only
page = db.execute('SELECT number, placed_on, total FROM orders ORDER BY placed_on DESC LIMIT 2').fetchall()
print('after columns per row:', len(page[0]))
for row in page:
print(row)
Output
before columns per row: 8
after columns per row: 3
('A-105', '2026-11-05', 50.0)
('A-104', '2026-11-04', 40.0)
The first query returns all eight columns for every order. The second returns three columns, sorted newest first, and stops after two rows. In a real app the same change applies to thousands of orders and to the join. The sample is small on purpose, so you can read every line. The table is in memory, so it disappears when the program ends.
Notice what the phone receives. In the second query, each row holds only the values the screen displays. The program does not need to filter or hide anything after the data arrives, because the database already did that work.
How Do You Spot It?
Spotting Extraneous Fetching takes evidence, not guesses. Microsoft's guide says the symptoms include high latency and low throughput. Users may report slow screens, or requests may time out and fail with HTTP 500 (internal server error) or 503 (service unavailable) responses. The guide also suggests reading the web server's event logs, which often hold the details behind those errors.
Tip
The main clue is a large gap. If a response carries far more data than the screen shows, investigate the query behind it.
Signs in Logs, Payloads, and Timings
Query logs
Look for queries that use SELECT *, or that have no WHERE and no LIMIT. Compare each column list with what the screen uses.
Payload sizes
Measure how many bytes each API response carries. A list endpoint that sends many fields the screen never displays is a suspect.
Response times
Track how long each endpoint takes. A slowdown that starts after more fields or rows were added to a table is a strong hint.
Row counts
Compare how many rows a query returns with how many the user actually sees. A big difference means the filter or limit is missing.
This lesson only mentions such tools. Dashboards and alerts are covered in other lessons.
Telling It Apart From Other Problems
Extraneous Fetching can look like other data problems. Check the size of the data first. If the gap between data sent and data shown is large, the fetch is the likely cause.
It is also easy to swing too far. Over-fetching is often a reaction to Chatty I/O, which means making too many small requests. Someone who fixes chatty calls by loading everything at once has traded one problem for another. The goal is the right amount of data per request, not the fewest requests at any cost.
Check Yourself
Quick check
A product list page shows each item's name and price. The API returns every column for all 50,000 products, and the browser keeps only the products in the current category. What is the main problem, and which two changes fix it best?
Quick check
A report endpoint counts orders placed last month. Its code runs SELECT * FROM orders, loads every row into a list, and then loops through the list in Python to count last month's orders. Which change removes the most waste?
Quick check
A team adds an internal_notes column to the users table. The profile API response then doubles in size, and the mobile profile screen, which shows only the name and photo, becomes slow. What is the best first change?
Key Takeaways
Ask for less
Fetch only the columns, rows, and fields that the current task uses. Name them in the query or the API response.
Let the database narrow
Filter, sort, and limit in the database, and page long lists, so the program receives a short and useful answer.
Check the evidence
Compare query logs, payload sizes, and response times with what the screen shows. A large gap points to Extraneous Fetching.
Sources
- Extraneous Fetching antipattern, Azure Architecture Center (Microsoft Learn)
- Busy Database antipattern, Azure Architecture Center (Microsoft Learn)
- Handling API Pagination: Fetching Large Data Sets, API7.ai
- Don't OFFSET Your SQL Query's Performance, HackerNoon
- Fix Slow EF Core Queries with Projections, Bytecrate
- Db2 SELECT * Anti-Pattern and Over-Fetching, Mainframe Master
- Selection vs Projection: Database Operations Explained, CompleteEra
- Avoiding the Extraneous Fetching Antipattern, DEV Community
Last updated on 18 November 2026
Related lessons
- System designBusy Database: Why Too Much Work on the Database Slows Everything
- System designData Management in System Design: Where Data Lives and How It Stays Correct
- System designBack Pressure: How an Overloaded System Tells Callers to Slow Down
- System designPerformance Antipatterns: Common Design Mistakes That Slow Systems Under Load
Learn with me
Every lesson is free. I add new ones as I learn. Have a question, or want help with your own work? Book a short call.