The N + 1 query problem is a data-access performance anti-pattern caused by navigating lazily loaded object associations one element at a time. One query loads the root objects, then an additional query loads each associated object or collection. For n associated records, the operation executes one query plus n more. section 1.2.5 section 12.2.1
Loading sellers for items
A JPQL query loads a list of Item entities with one SQL SELECT. Iterating over the items and calling item.getSeller().getUsername() triggers another SELECT for each seller. A lazily loaded bids collection produces the same pattern: checking item.getBids().size() loads each collection separately; 100 bids can therefore require 101 SQL queries. section 12.2.1
Path: Ch12/nplusoneselects/src/test/java/com/manning/javapersistence/ch12 /nplusoneselects/NPlusOneSelects.java List<Item> items = em.createQuery("select i from Item i").getResultList(); // select * from ITEM for (Item item : items) { assertNotNull(item.getSeller().getUsername()); #1 // select * from USERS where ID = ? } #1 Whenever we access a seller, each of them must be loaded with an additional <kbd>SELECT</kbd>.
Inspect the complete seller example showing the root Item query, the loop, and the additional user SELECT. section 12.2.1
Choose a fetching strategy according to the procedure that will use the data:
- Keep associations lazy by default when appropriate, but recognize that on-demand navigation can generate too many small SQL statements. section 12.2
- Avoid replacing every lazy association with eager loading: larger queries may reduce statement count while creating oversized Cartesian-product result sets. section 1.2.5 section 12.2.1
- Define global mapping defaults, then override them for particular procedures with a custom JPQL query, CriteriaQuery, or SQL query. section 12.2
