liveBook · Manning

N + 1 query problem

As explained in Troubleshooting Java: Read, debug, and optimize JVM applications

A performance problem in which retrieving a collection triggers one initial query followed by a separate query for each retrieved record.

An N + 1 query problem occurs when an initial query retrieves records, then a separate query runs for each record. Instead of one query to fetch related data, the application sends the initial query plus N more, often adding significant latency. section 7.3.2

When a persistence framework generates SQL behind the scenes, use a profiler to inspect the queries and their execution counts. A profiler’s query list can make repeated queries easier to spot than logs or a debugger. section 7.3.2

Figure 7.13 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 as you can when using JDBC and native queries.

Shows how a profiler can intercept SQL generated by a framework. section 7.3.2

How Troubleshooting Java: Read, debug, and optimize JVM applications covers it

How deep it goes
Defines the initial-plus-N pattern and explains profiler-based detection, but does not describe a fix.
Examples
Describes fetching records with an initial query followed by one query per record.
What it assumes
Assumes familiarity with persistence frameworks and SQL, while defining the initial-plus-N query pattern.
What you can do after
Diagnose repeated framework-generated queries by inspecting profiler query counts and execution times.
How it connects
Connects persistence frameworks with SQL profiling, logs, debugging, and CPU profiling.

Also explained in

Compare how 5 books explain N + 1 query problem →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Troubleshooting Java: Read, debug, and optimize JVM applications. The section links point to the parts of the book it draws on.