N+1 queries are a well-liked downside in lots of purposes that run SQL queries. The issue may be described simply as follows:
- 1 question fetching a mother or father worth is run
- N queries fetching every particular person little one values are run
This downside isn’t restricted to SQL, it could actually occur with any poorly designed API that doesn’t permit for batch and/or bulk processing (even saved procedures). However with SQL, it’s notably painful, as a result of in lots of circumstances, working tons of logic in a single question could be completely potential, particularly with jOOQ’s MULTISET and SQL/XML or SQL/JSON help.
Within the worst case, the N+1 downside is brought on by a 3rd occasion ORM – or quite, its poor implementation / configuration, however some ORMs make it very easy to shoot oneself within the foot with N+1 issues…
An instance
Let’s follow JDBC for now for instance how N+1 queries occur.
strive (Assertion stmt = conn.createStatement()) {
// The mother or father question, fetching actors
strive (ResultSet r1 = stmt.executeQuery(
"""
SELECT actor_id, first_name, last_name
FROM actor
LIMIT 5
"""
)) {
whereas (r1.subsequent()) {
System.out.println();
System.out.println(
"Actor: " + r1.getString(2) + " " + r1.getString(2));
// The kid question, fetching movies per actor
strive (PreparedStatement pstmt = conn.prepareStatement(
"""
SELECT rely(*) FROM film_actor WHERE actor_id = ?
"""
)) {
pstmt.setInt(1, r1.getInt(1));
strive (ResultSet r2 = pstmt.executeQuery()) {
whereas (r2.subsequent()) {
System.out.println("Movies: " + r2.getInt(1));
}
}
}
}
}
}
When run in opposition to the sakila database, the above prints:
Actor: PENELOPE PENELOPE Movies: 19 Actor: NICK NICK Movies: 25 Actor: ED ED Movies: 22 Actor: JENNIFER JENNIFER Movies: 22 Actor: JOHNNY JOHNNY Movies: 29
Clearly right, however we may have simply run this in a single question:
SELECT
a.first_name,
a.last_name,
rely(fa.film_id)
FROM actor AS a
LEFT JOIN film_actor AS fa USING (actor_id)
GROUP BY a.actor_id
Provided that we have now 200 actors in whole, which do you like? Working 1+200 queries or simply 1 question? In case you’re in command of your SQL, this error is far much less more likely to occur, however what in the event you’re not in (full) management, as a result of the SQL is generated based mostly on keen/lazy loading configurations and sophisticated entity graph annotations, then you definately’ll be completely satisfied you’ll be able to simply plug in jOOQ’s DiagnosticsConnection’s repeated statements diagnostic into your integration check atmosphere (not essentially in manufacturing, as there’s some overhead to parse and normalise all of the SQL strings).
Utilized to the above JDBC instance:
DSLContext ctx = DSL.utilizing(connection);
ctx.configuration().set(new DefaultDiagnosticsListener() {
@Override
public void repeatedStatements(DiagnosticsContext c) {
// Customized callback, can also throw exceptions, and so on.
System.out.println(
"Repeated assertion: " + c.normalisedStatement());
}
});
Connection conn = ctx.diagnosticsConnection();
You’re now getting the next output:
Actor: PENELOPE PENELOPE Movies: 19 Actor: NICK NICK Repeated assertion: choose rely(*) from film_actor the place actor_id = ?; Movies: 25 Actor: ED ED Repeated assertion: choose rely(*) from film_actor the place actor_id = ?; Movies: 22 Actor: JENNIFER JENNIFER Repeated assertion: choose rely(*) from film_actor the place actor_id = ?; Movies: 22 Actor: JOHNNY JOHNNY Repeated assertion: choose rely(*) from film_actor the place actor_id = ?; Movies: 29
As you’ll be able to see, the diagnostics connection begins logging after the primary repetition of the assertion, the belief being that inside a transaction, it’s usually pointless to ever repeat an announcement greater than as soon as, as a result of there’s nearly all the time a greater manner.
Utilizing this with JPA / Hibernate
You most likely don’t write JDBC statements manually like this, but it surely doesn’t matter who calls JDBC (you, jOOQ, JdbcTemplate, Hibernate, and so on.). In case you proxy your connection (or DataSource) with jOOQ’s DiagnosticsConnection or DiagnosticsDataSource, then you’ll be able to intercept such occasions simply, irrespective of the trigger.
Future variations of jOOQ will add much more diagnostics through https://github.com/jOOQ/jOOQ/points/7527.
To see what’s out there in jOOQ already, check with the guide.
