N+1 queries are a preferred downside in lots of purposes that run SQL queries. The issue may be described simply as follows:
- 1 question fetching a mum or dad worth is run
- N queries fetching every particular person baby values are run
This downside isn’t restricted to SQL, it could occur with any poorly designed API that doesn’t enable for batch and/or bulk processing (even saved procedures). However with SQL, it’s significantly painful, as a result of in lots of circumstances, operating tons of logic in a single question can be completely attainable, 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 reasonably, 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 keep on with JDBC for now for instance how N+1 queries occur.
strive (Assertion stmt = conn.createStatement()) {
// The mum or dad 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 appropriate, 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
On condition that we’ve got 200 actors in complete, which do you favor? Operating 1+200 queries or simply 1 question? If you happen to’re in command of your SQL, this error is way much less prone 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 complicated entity graph annotations, then you definitely’ll be pleased you may simply plug in jOOQ’s DiagnosticsConnection’s repeated statements diagnostic into your integration take a look at surroundings (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, might also throw exceptions, and so forth.
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 may see, the diagnostics connection begins logging after the primary repetition of the assertion, the belief being that inside a transaction, it’s typically pointless to ever repeat an announcement greater than as soon as, as a result of there’s nearly all the time a greater means.
Utilizing this with JPA / Hibernate
You in all probability don’t write JDBC statements manually like this, but it surely doesn’t matter who calls JDBC (you, jOOQ, JdbcTemplate, Hibernate, and so forth.). If you happen to proxy your connection (or DataSource) with jOOQ’s DiagnosticsConnection or DiagnosticsDataSource, then you may 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, seek advice from the handbook.
