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.
- 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
context
resolving link…