Sunday, March 3, 2024

Utilizing jOOQ’s DiagnosticsConnection to detect N+1 Queries – Java, SQL and jOOQ.


N+1 queries are a preferred drawback in lots of purposes that run SQL queries. The issue might be described simply as follows:

  • 1 question fetching a guardian worth is run
  • N queries fetching every particular person youngster values are run

This drawback isn’t restricted to SQL, it could occur with any poorly designed API that doesn’t permit 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 could be completely potential, particularly with jOOQ’s MULTISET and SQL/XML or SQL/JSON help.

Within the worst case, the N+1 drawback is brought on by a 3rd occasion ORM – or fairly, 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 persist with JDBC for now for example how N+1 queries occur.

attempt (Assertion stmt = conn.createStatement()) {

    // The guardian question, fetching actors
    attempt (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
            attempt (PreparedStatement pstmt = conn.prepareStatement(
                """
                SELECT rely(*) FROM film_actor WHERE actor_id = ?
                """
            )) {
                pstmt.setInt(1, r1.getInt(1));

                attempt (ResultSet r2 = pstmt.executeQuery()) {
                    whereas (r2.subsequent()) {
                        System.out.println("Movies: " + r2.getInt(1));
                    }
                }
            }
        }
    }
}

When run towards 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 might 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 have now 200 actors in whole, which do you favor? Working 1+200 queries or simply 1 question? For those who’re in command of your SQL, this error is far much less more likely to occur, however what for those who’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, you then’ll be blissful you possibly can 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, may 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 possibly can see, the diagnostics connection begins logging after the primary repetition of the assertion, the idea being that inside a transaction, it’s typically pointless to ever repeat a press release greater than as soon as, as a result of there’s virtually at all times a greater method.

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.). For those who proxy your connection (or DataSource) with jOOQ’s DiagnosticsConnection or DiagnosticsDataSource, then you possibly can intercept such occasions simply, regardless of the trigger.

Future variations of jOOQ will add much more diagnostics by way of https://github.com/jOOQ/jOOQ/points/7527.

To see what’s accessible in jOOQ already, consult with the guide.

Related Articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Latest Articles