N + 1 query problem

As explained in Troubleshooting Java, Second Edition

The N + 1 query problem is an access pattern in which one initial query is followed by a separate query for each of N retrieved records.

The N + 1 query problem occurs when an initial query retrieves N records and the application then issues one additional query for each record. The resulting N + 1 queries can create significant latency. section 6.3.2

Investigate the pattern by asking:

• How many times does each SQL query execute? section 6.3.2
• Does a framework fetch related table data in one composed query or issue a query per retrieved record? section 6.3.2
• Can a profiler reveal execution time and query counts more clearly than logs or a debugger? section 6.3.2

Figure 6.15 Even when working with a framework, the profiler can still intercept the SQL queries. This makes your investigation a lot easier because you can’t copy the query directly from the code like when using JDBC and native queries.

Use profiler interception to inspect SQL generated behind a persistence framework, including execution time and execution count. section 6.3.2

How Troubleshooting Java, Second Edition covers it

How deep it goes

Defines the access pattern and explains profiler-based detection, but stops short of prescribing a specific code fix.

Examples

Uses multi-table framework fetching and profiler query counts to expose one initial query followed by N additional queries.

What you can do after

Diagnose/debug with a profiler by locating repeated queries, execution counts, and latency.

How it connects

Connects persistence frameworks with SQL queries, profilers, latency, logs, and debuggers.

liveBookmanning.com

Read Troubleshooting Java, Second Edition on liveBook Get the book at manning.com

Also explained in

• 

  liveBookmanning.com
  Kubernetes for DevelopersThe N+1 query problem is a database access pattern in which one query retrieves a collection and additional queries retrieve related data for each item.
• 

  liveBookmanning.com
  Java Persistence with Spring Data and Hibernate epubThe n+1 selects problem is a data-access performance anti-pattern in which one query retrieves a root object and an additional query retrieves each associated object or collection element.
• 

  liveBookmanning.com
  ScyllaDB in ActionThe N+1 problem is a performance problem in which loading a collection and related data requires one initial query plus a separate query for each item.

Compare how 4 books explain N + 1 query problem →

Related concepts

• lazy initializationproduced by · Lazy initialization is a technique that postpones creating, retrieving, computing, or loading a value or resource until it is needed.
• eager loadingaddressed by · Eager loading is a data-loading strategy that retrieves required data before it is requested individually, often fetching related data together with its primary entity.
• batchingaddressed by · Batching is the grouping of multiple data items, requests, or operations so they can be processed together as a unit.
• object-relational mapperproduced by · An object-relational mapper is software that maps programming-language objects and operations to relational database structures and operations.

This explainer was generated by Manning's knowledge graph from the text of Troubleshooting Java, Second Edition. The section links point to the parts of the book it draws on.