You find code that loads every row of a table into a List with a JPQL query and then filters and counts it in Java. Why is that a problem, and what should the code do instead?
answer
- query returns everything, Java keeps twelve
- entity + snapshot per row = heap multiple
- predicate never reaches the index
- count(e) not getResultList().size()
- stream/page + clear() for real bulk work
basics
~20 sThe database sends every row over the network and Hibernate turns each into a managed entity, so memory and time scale with table size and no index is used. Push the filter, the aggregate and the limit into the query instead.
solid answer
~60 sThree separate costs stack up: 1. **Transfer** — every row crosses the network, however few are kept. 2. **Materialisation** — Hibernate instantiates an entity per row and keeps a load-time snapshot for dirty checking, so heap use is a multiple of the raw data. 3. **No index** — the predicate never reaches the database, so it cannot use an index; the engine does a full scan and the application does the comparison. And it degrades invisibly: with 500 rows in a test database it looks fine, at 5 million it is an OutOfMemoryError. The fix is to express the intent in the query: - filtering → a `where` clause with bound parameters; - counting → `select count(e) ...`, never `getResultList().size()`; - "the newest one" → `order by` plus `setMaxResults(1)`; - sums and grouping → `sum`/`group by`; - "does one exist" → `select count(e)` or an `exists` subquery. If a genuinely large set must be processed, page it or stream it rather than materialising it, and clear the persistence context as you go.
code
java · 10 lines// anti-pattern: whole table into heap, filtered in Java
List<Customer> all = em.createQuery("select c from Customer c", Customer.class)
.getResultList();
long active = all.stream().filter(c -> c.getStatus() == ACTIVE).count();
// fix: the database answers the question it was asked
long active = em.createQuery(
"select count(c) from Customer c where c.status = :st", Long.class)
.setParameter("st", ACTIVE)
.getSingleResult();go deeper
Say plainly that the database should do the filtering and counting, and name the query equivalents — WHERE, COUNT, ORDER BY with a limit.
Explain the three costs — transfer, entity materialisation with dirty-check snapshots, and the lost index access — and mention that flush cost also rises with managed-entity count.
Add the scaling-bomb framing, the correct pattern for genuine bulk work (chunking, streaming, clear()), and how you would catch it in review or tests.
Treat it as a boundary rule: query layers return bounded, projected results, unbounded reads are forbidden without an explicit batching path, and tests enforce row-count budgets.
## The shape of the anti-pattern ```java List<Customer> all = em.createQuery("select c from Customer c", Customer.class).getResultList(); long active = all.stream().filter(c -> c.getStatus() == ACTIVE).count(); ``` The database was asked for everything; the decision about what mattered was made afterwards in Java. Variants: `getResultList().size()` for a count, `stream().max(...)` for the newest row, `stream().mapToLong(...).sum()` for a total, `contains()` for an existence check, `subList` for a page. ## Why it is expensive **Network and serialization.** Every column of every row is encoded by the database, sent over the wire and decoded by the driver. For a million-row table with wide columns this is easily hundreds of megabytes for a result the caller reduces to a single number. **Entity materialisation.** Hibernate does not hand back rows; it builds managed entities. Each one costs the object itself, its collections' placeholders, an entry in the persistence context, and a **load-time snapshot** of its state used later for dirty checking. A rough rule of thumb is several times the raw row size in heap. All of it is garbage a millisecond later. **Lost index access.** A predicate the database never receives cannot be answered by an index. `where status = 'ACTIVE'` against an index touches only matching rows; filtering in Java forces a full scan of the table regardless. **Lost aggregation.** Databases compute `count`, `sum`, `max` and `group by` while streaming, without materialising anything. Doing it in Java means materialising everything first. **Flush cost.** Because the entities are managed, every subsequent flush in that persistence context dirty-checks all of them, so an unrelated write later in the same unit of work becomes slow too. **A silent scaling bomb.** Nothing is wrong at 500 rows. The behaviour is correct at every size; only the cost changes. That is why this survives code review and shows up as a production incident. ## The replacements | Intent | Wrong | Right | |---|---|---| | filter | load all, `stream().filter` | `where` clause with bound parameters | | count | `getResultList().size()` | `select count(c) from Customer c where ...` | | exists | `list.isEmpty()` | `select count(c) ...` or an `exists` subquery | | newest | `stream().max(...)` | `order by ... desc` + `setMaxResults(1)` | | sum / group | `stream().collect(groupingBy)` | `select c.status, sum(c.total) ... group by c.status` | | page | `subList(a, b)` | `setFirstResult` / `setMaxResults` | Always bind parameters (`setParameter`) rather than concatenating values into the query string — concatenation is both a plan-cache killer and, for anything derived from user input, an injection risk. ## When you really do need many rows Batch jobs, exports and migrations legitimately touch large sets. The rule is not "never read a lot", it is "never hold a lot": - **Page through** with an ordered key so each chunk is bounded. - **Stream** the result rather than materialising a list, so rows are processed as they arrive. - **`em.clear()` between chunks**, otherwise the persistence context grows exactly as if you had loaded everything. - **Prefer projections** (or a stateless read path) when the job does not need managed entities at all. ## A useful heuristic Any time a Java collection operation appears immediately after `getResultList()`, ask whether the database could have done it. `filter`, `count`, `max`, `sum`, `sorted`, `distinct`, `limit` and `skip` all have direct query equivalents, and the query version does the work where the data already lives.
- The code only needs to know whether any matching row exists. What is the cheapest query?A count restricted to the predicate, or an exists subquery — 'select count(c) from Customer c where c.status = :st' compared to zero, or a query returning a constant with setMaxResults(1). Both let the database stop early and neither materialises entities. Loading the list and calling isEmpty() forces every matching row to be transferred and turned into a managed entity just to answer a boolean.
- A nightly job genuinely has to process every row. How do you do that without loading the table into memory?Process it in bounded chunks: page with an ordered key (or stream the result set) so only a chunk is materialised at a time, and call clear() on the persistence context after each chunk so processed entities become garbage. Read-only or projection-based access avoids the dirty-checking snapshots entirely. The goal is that heap use is proportional to the chunk size, not to the table size.
It is like phoning a warehouse and having them ship you the entire inventory so you can pick out one box in your driveway, instead of telling them the item number.
saying these in an interview costs you the question
- Calling getResultList().size() to obtain a count
- Claiming it is fine because 'the table is small' with no bound enforcing that
- Believing Hibernate lazily streams a getResultList() so memory is not an issue
- Replacing the Java filter with a filter over a cached list instead of a WHERE clause
- Fixing the symptom by raising the heap